Dynamic Combobox List Won't show Any List (Adapt from Contextures) Help!

Linkback : http://www.mrexcel.com/forum/excel-q...ures-help.html

Hi All,

I have encounter an error (result in blank combobox list), while using dynamic data validation list, and adapt it to combobox list. it only works for only common data validation list.

the list placed on different sheet :
FORUM2.JPG

for the dynamic data validation list, I'm using formula :

Formula:

=INDIRECT(VLOOKUP(B3,Named_Range,2,FALSE))

the combobox result an empty
FORUM3.JPG

My excel file is here :


My code is :

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
On Error GoTo errHandler


    Set cboTemp = ws.OLEObjects("ComboBox1")
      On Error Resume Next
      With cboTemp
        .ListFillRange = ""
        .LinkedCell = ""
        .Visible = False
      End With


    On Error GoTo errHandler
      If Target.Validation.Type = 3 Then
        Cancel = True
        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 + 15
          .Height = Target.Height + 5
          .ListFillRange = str
          .LinkedCell = Target.Address
        End With
        cboTemp.Activate
        'open the drop down list automatically
        Me.ComboBox1.DropDown
      End If


errHandler:
  Application.EnableEvents = True
  Exit Sub


End Sub

the link source for
combo box list :
http://www.contextures.com/xlDataVal...Combo#AddCombo

dynamic data validation list :
http://www.contextures.com/xlDataVal02.html

thanks a lot for the help,
Warm regards

Adrian

Hi whitfield,

in my excel, the combobox result empty list. how come?

Solved... Just need a simple modification on the code, no additional code at all.

whoops...

here's the code

If Not Intersect(Target, Target.Worksheet.Range("C:C")) Is Nothing Then
    Set cboTemp = ws.OLEObjects("ComboBox1")
'      On Error Resume Next
      With cboTemp
        .ListFillRange = ""
        .LinkedCell = ""
        .Visible = False
      End With

'    On Error GoTo errHandler
      If Target.Validation.Type = 3 Then
        Cancel = True
        Application.EnableEvents = False
        str = Cells(Target.Row, 2).Value
        str = Right(str, Len(str))
        With cboTemp
          .Visible = True
          .Left = Target.Left
          .Top = Target.Top
          .Width = Target.Width + 15
          .Height = Target.Height + 5
          .ListFillRange = str
          .LinkedCell = Target.Address
        End With
        cboTemp.Activate
        'open the drop down list automatically
        Me.ComboBox1.DropDown
      End If
End If