ExcelBanter - Data Validation and List Length

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
'===========================

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

--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html