Posts by zura04

Re: Combo Boxes Not Appearing

that is the first thing I tried and does not work - if I change the column width, the pulldowns are still wide.

Re: Combo Boxes Not Appearing

All I want to do is make the pulldown menu boxes (created via data validation list) narrower - make them only as wide as the longest text entry in the list. Right now they are quite wide even if the column they are in is narrow. It is just an aesthetic formatting thing. Someone had suggested earlier that maybe this could be done with combo boxes but if there is a way to do it without combo boxes, even better.

Re: Combo Boxes Not Appearing

Tried it... the combo boxes still aren't populating. For reference this is what my code looks like now:

'==========================
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, _
  Cancel As Boolean)
Dim str As String
Dim cboTemp As OLEObject
Dim ws As Worksheet
Set ws = ActiveSheet
Cancel = True
Set cboTemp = ws.OLEObjects("ComboBox1")
  On Error Resume Next
  With cboTemp
  'clear and hide the combo box
    .ListFillRange = ""
    .LinkedCell = ""
    .Visible = False
  End With
On Error GoTo errHandler
  If Target.Validation.Type = 3 Then
    'if the cell contains a data validation list
    Application.EnableEvents = False
    'get the data validation formula
    str = Target.Validation.Formula1
    str = Right(str, Len(str) - 1)
    With cboTemp
      'show the combobox with the list
      .Visible = True
      .Left = Target.Left
      .Top = Target.Top
      .Width = Target.Width + 5
      .Height = Target.Height + 5
      .ListFillRange = ActiveWorkbook.Names(str).RefersToRange.Parent.Name & "!" & _
ActiveWorkbook.Names(str).RefersToRange.Address
      .LinkedCell = Target.Address
    End With
    cboTemp.Activate
  End If
  
errHandler:
  Application.EnableEvents = True
  Exit Sub


End Sub
'=========================================
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim str As String
Dim cboTemp As OLEObject
Dim ws As Worksheet
Set ws = ActiveSheet


Set cboTemp = ws.OLEObjects("ComboBox1")
  On Error Resume Next
If cboTemp.Visible = True Then
  With cboTemp
    .Top = 10
    .Left = 10
    .ListFillRange = ""
    .LinkedCell = ""
    .Visible = False
    .Value = ""
  End With
End If


errHandler:
  Application.EnableEvents = True
  Exit Sub


End Sub
'====================================

Re: Combo Boxes Not Appearing

thank you. I'm not exactly sure where to paste it in, it says "ListFillRange" in a few places.

Re: Combo Boxes Not Appearing

Good call!

However, I can't name a range, because then I get the "You may not use references to other worksheets or workbooks for Data Validation criteria" message. My main sheet is Sheet1 and my lists are on Sheet2.

Re: Combo Boxes Not Appearing

Is there a tutorial somewhere that shows you how to do that?? I am (obviously) confused as well.

Alternately, is there just a way to make the data validation dropdown menus narrower without using combo boxes?

Re: Combo Boxes Not Appearing

I just made a quick sample of what I am working with.

In this sample there is a text-fill-in column next to a column that uses data validation dropdown menus. The data validation lists are on a protected Sheet2.

Now what I did was I opened the Control Toolbox, clicked the Combo Box button, went into Design Mode, and the only thing I changed from the default properties on the combo box was changing AutoSize to "True".

Then I exited Design Mode and cut-and-paste the code from

http://www.contextures.com/xlDataVal10.html

into the sheet (via right clicking on the sheet and going to View Code)

Saved thatm went back to the workbook. When you double-click on one of the data validation dropdown menus now, a combo box appears like it's supposed to, but it is not populated with the data validation fields. You can still see the data validation dropdown under the combo box.

You will see that the second column, "Choose An Item", is rather narrow but the dropdown menu box is wide. All I want to do is make it so that the width of the drodown menu box is only as wide as its longest text item.

I'm attaching the file now - please let me know what you think!

Thanks!

Hi all,

I am posting this is in a new thread because it is not really relevant to my old thread.

I am trying to make some of my data validation dropdown list boxes narrower. I was told one way to do that is to use combo boxes. (If there are any other ways, please let me know!)

I don't think my combo boxes are working. I had tried to make a combo box appear in each data validation pulldown cell by using this tutorial:

http://www.contextures.com/xlDataVal10.html

I followed this tutorial word for word, but when I try to test the code at the end by double-clicking on a data validation pulldown cell, no combo box appears. Not sure how to remedy this! Any help greatly appreciated.

Thanks!

Re: Narrowing Data Validation Dropdown List

Ok I get that.. but I don't think my combo boxes are working. I had tried to make a combo box appear in each data validation pulldown cell by using this tutorial:

http://www.contextures.com/xlDataVal10.html

I followed this tutorial word for word, but when I try to test the code at the end by double-clicking on a data validation pulldown cell, no combo box appears. Not sure how to remedy this!

Re: Narrowing Data Validation Dropdown List

Thank you - I just did that - unfortunately - it didn't work! The dropdown boxes are still really wide.

Re: Narrowing Data Validation Dropdown List

thanks bryce! now what about the thing that says 'declarations'?

Re: Narrowing Data Validation Dropdown List

anyone? I know that other guy is not willing to help me any longer, but I'd like to know if:

- there's a way to do this w/o using VB code

- and if not, where specifcally to paste this code:

http://www.freevbcode.com/ShowCode.Asp?ID=1800

I can't figure it out - not familiar with VB and don't know where to paste the 'declarations' versus the 'code'.

thanks!

Re: Narrowing Data Validation Dropdown List

okay - here is the link to the code.

http://www.freevbcode.com/ShowCode.Asp?ID=1800

I just am not sure what to put where.

Re: Narrowing Data Validation Dropdown List

I can't even find it at this point, I just pulled it off some random site yesterday. I'd rather know what to do and how to do it rather than just cutting and pasting some code.

Re: Narrowing Data Validation Dropdown List

I don't know if it's right and I don't know where to paste it... I tried both ways you suggested and they didn't work.

Basically, I have a bunch of columns where in each cell, you need to make a selection from a data validation pulldown list. I'm trying to make the pulldown lists narrower, preferably ending at the length of the longest text option in the pulldown. Does anyone know how to do this?

Re: Narrowing Data Validation Dropdown List

If someone could just point me in the right direction of how to make the dropdown lists narrower that would be great...

thanks!

Hi!

I want to make some of my data validation dropdown lists narrower, so they end at the length of their longest list entry.

I know NOTHING about VB and although I found some "free code" online that claims to do just what I want to do above, I have no idea how to paste it in or where to paste it in, etc.

Can someone please tell me where to paste in this code. Like step-by-step, in layman's terms.

Thanks!

Hi all,

I need help! I created a dropdown menu using Data Validation and a dynamic range, so that anything typed into the range automatically appears on the dropdown.

The formula I used for this was a Defined Name of:

=OFFSET('Priority List'!$C$6,1,0,COUNTA('Priority List'!$A:$A),1)

Where C6 is the header cell of the column and I want the range to go on to the end.

What I want to know is how I can filter that dropdown so that when I select "A" from the dropdown menu, all the rows that do not have "A" in the C-column are hidden (like what happens with Autofilter). Is there any way to do this?

I am pretty new to the tough aspects of Excel, so please try to keep it relatively simple.

Thanks!