Validation List: Wide as Widest List Item

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: 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!

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

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

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

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

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

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