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

[radian89]

Hi All,

I have encounter an error, 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 :

for the dynamic data validation list, I'm using formula :
=INDIRECT(VLOOKUP(B3,Named_Range,2,FALSE))

the combobox result an empty

My excel file can be download in :
http://s000.tinyupload.com/?file_id=89813714909094817910

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/xlDataVal10.html#AddCombo#AddCombo

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

thanks a lot for the help,
Warm regards

Adrian

 

[dmt32]

Hi,
quick glance at your code & that published by contextures shows:

this line in your code:

.ListFillRange = str

is not same as this in contextures code:

.ListFillRange = ws.Range(str).Address

which probably explains why you are getting nothing showing.

You may do well to re-install ALL the code published by contextures for this application.

Hope Helpful

Dave

 

[radian89]

Hi dmt32,

thanks for the reply, I've tried it, but it's not success.

actually I'm following, contextures for list in different sheet

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

which result for str

best,
Adrian

 

[radian89]

solved this riddle, with a simple code modification. no need additional code.