Dependant Combobox with Data Validation using Dynamic ranges

Question

Good afternoon,

Hopefully someone can help me out.  I've been spinning my wheels on this challenge for a couple weeks now and have yet to find a solution that will work for my application.  Others seem to have asked the question on many other forums but never seem to come to a final answer before the thread is abandoned.

I'm experiencing a similar challenge to that which is described in the following thread:

http://answers.microsoft.com/en-us/office/forum/office\_2010-customize/excel-2010-incorporating-combobox-for-data/aae13c39-b12b-4d9b-8410-6e957ecba8e6?page=1&tm=1433357538044

The difference is that I am using the single click version of contextures floating combobox.  When simply using just data validation along with the INDIRECT function the dependent drop down list's work great but as soon as I turn on the combobox all I get is a blank.  Below is the code being used.   Any guidance would be much appreciated.

Worksheet code:

Option Explicit

Private Sub TempCombo_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)

On Error Resume Next

' Move to next cell if Tab or Enter are pressed

    Select Case KeyCode

        Case 9 'Tab

            ActiveCell.Offset(0, 1).Activate

        Case 13 'Enter

            ActiveCell.Offset(1, 0).Activate

    End Select

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

    Application.EnableEvents = False

    Application.ScreenUpdating = True

    If Application.CutCopyMode Then

        'allow copying and pasting on the worksheet

        GoTo errHandler

    End If

    ShowAutocomplete Target

errHandler:

    Application.EnableEvents = True

    Exit Sub

Private Sub TempCombo_Change()

On Error Resume Next

  ActiveCell.Value = CStr(ActiveCell.Value)

End Sub

Global Code:

Public Sub ShowAutocomplete(Target As Range)

    Dim strVF As String

    Dim cboTemp As OLEObject

    Dim ws As Worksheet

    On Error GoTo errHandler

    Set ws = ActiveSheet

    'Set ws = Me

    Set cboTemp = ws.OLEObjects("TempCombo")

    'On Error Resume Next

    With cboTemp

        ' Clear and hide the combo box

        .ListFillRange = ""

        .LinkedCell = ""

        .Visible = False

    End With

    If Target.Validation.Type = 3 Then

        ' The cell contains a data validation list

        Application.EnableEvents = False

        ' Get the data validation formula

        strVF = Target.Validation.Formula1

        strVF = Right(strVF, Len(strVF) - 1)

        With cboTemp

            ' Show the combobox with the list

            .Visible = True

            .Left = Target.Left

            .Top = Target.Top

            .Width = Target.Width + 15

            .Height = Target.Height + 5

            .ListFillRange = strVF

            .LinkedCell = Target.Address

        End With

        cboTemp.Activate

        ' Open the drop down list automatically

        ActiveSheet.TempCombo.DropDown

    End If

    Application.EnableEvents = True

    On Error GoTo 0

    Exit Sub

errHandler:

    Application.EnableEvents = True

    ' If it's 1004 there's no data validation in the cell

    If Err.Number <> 1004 Then

        MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure ShowAutocomplete"

    End If

End Sub

Answer 1

The formula would be something like =INDIRECT("A1") where the value in A1 equals a range name that lives on another worksheet.

OK. What I have done for the testing of the code below.

1. Created a list on another worksheet.
2. Create a defined name  called cboList for the list on the other worksheet.
3. Cell A1 contains cboList  (Nothing else and no equals sign)
4. Cell C1 contains list type data validation with formula =INDIRECT(A1)
5. Created a ComboBox over top of cell C1.

A formula cannot be used for the ListFillRange so the formula needs to be evaluated so that the defined name can be used for the ListFillRange.

To see what the code does to extract the defined name for the combo box ListFillRange, uncomment the lines with stop and each time the code stops, hover the cursor over strList to see its value. I think this is easier than trying to explain in words what the code does. (To restart the code after the stop press F5 while in the VBA editor)

I understand that you have said that you cannot access OneDrive from work but I have uploaded a zipped copy of my test workbook to the following link so if you can access it from somewhere else then it might help.

http://1drv.ms/1Gxybr0

If you cannot access it then if you provide me with an email address then I will email it to you. This forum automatically deletes email addresses so provide it as follows.

name AT DomainName DOT com

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

    'Exit Sub   'Uncomment to suppress event during development

    Dim strList As String

    Application.EnableEvents = False

    'Test if Target cell contains List type validation

    'The If test line will error if not a validation cell and goto SkipProcess

    On Error GoTo SkipProcess

    If Target.Validation.Type = 3 Then

        'Assign the List formula to a string variable

        strList = Target.Validation.Formula1

        'Stop

        'Remove equal sign

        strList = Replace(strList, "=", "")

        'Stop

        'Remove Indirect

        strList = Replace(strList, "INDIRECT", "")

        'Stop

        'Evaluate what is in A1 to extract the defined name

        strList = Evaluate(strList)

        'Stop

        'Assign the defined name to the combo listfillrange

        Me.ComboBox1.ListFillRange = strList

        Me.ComboBox1.Visible = True

        Me.ComboBox1.DropDown

        Application.EnableEvents = True

        Exit Sub    'Skips making combo invisible

    End If

SkipProcess:

    Me.ComboBox1.Visible = False

    Application.EnableEvents = True

End Sub

Private Sub ComboBox1_Change()

    Application.EnableEvents = False

    Me.ComboBox1.Visible = False

    Application.EnableEvents = True

End Sub

Answer 2

Hello jayg287,

My apologies for not getting back to you sooner after telling you I would monitor your user name for the new thread but I have been away for a few days and tied up with other commitments. However, I will still try to assist.

Can you please post the Data Validation formula that you are using because I am thinking that it is a bit more complex than a simple list and hence this might be the source of the problem.

Answer 3

Hi OssieMac,

The formula would be something like =INDIRECT("A1") where the value in A1 equals a range name that lives on another worksheet.

Thanks

Answer 4

Ok thank you very much.  I'll download it from home tonight and report back.