Data Validation and List Length

[Tom Kolkmeier]

I'm trying to make a combo box that allows me to display more than
rows of data in the drop down list. I copied/pasted macro from sampl
file listed at www.contextures.on.ca/excelfiles.html#DataVal, but
can't get combo box to "behave" properly in my spreadsheet. I name
the combo box "TempCombo" to match the macro name. When I double clic
on the cell containing my datavalidation list, the macro initiates th
combo box, but the box doesn't insert the "LinkedCell" an
"ListFillRange" like it's supposed to. Anyone try to use this before
Thanks

 

[Debra Dalgleish]

The sample workbook uses a list on the same worksheet. If you're using a
named range on a different sheet, you could add a reference to that
sheet. For example:

'===========================
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As
Boolean)
Dim str As String
Dim cboTemp As OLEObject
Dim ws As Worksheet
Dim wsList As Worksheet
Set ws = ActiveSheet
Set wsList = Sheets("Sheet2")
Cancel = True
Set cboTemp = ws.OLEObjects("TempCombo")
On Error Resume Next
With cboTemp
.ListFillRange = ""
.LinkedCell = ""
.Visible = False
End With
On Error GoTo errHandler
If Target.Validation.Type = 3 Then
Application.EnableEvents = False
str = Target.Validation.Formula1
str = Right(str, Len(str) - 1)
With cboTemp
.Visible = True
.Left = Target.Left
.Top = Target.Top
.Width = Target.Width + 5
.Height = Target.Height + 5
.ListFillRange = wsList.Range(str).Name
.LinkedCell = Target.Address
End With
cboTemp.Activate
End If

errHandler:
Application.EnableEvents = True
Exit Sub

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