Excel VBA Replace data validation lists with combo box

Question

I have a worksheet with four data validation drop downs lists. I want to replace these four data validation drop down lists with four dependent / cascading combo boxes. I don't want to use userforms!

I have played around with a combo box in my worksheet and I think that it only accepts vertical lists. In this thread there might be a workaround: Excel VBA: Why does combo box shows only one item in the list?

I also want my combo boxes to be dependent / cascading. In this thread there could be a solution in VBA: How to list combo box data based on another combo box entry

Also, I am using the cell range / address of the four data validation drop downs to perform vlookup. How can I reference one/two/three/four combo boxes in a vlookup formula?

I would like help with:

Create two combo boxes from one horizontal list.
Create two dependent combo boxes from the selection of the first two combo boxes from horizontal lists.
Reference the four values of the four combo boxes in multiple vlookups formulas. Say I select Sweden in my combo box I want to use the value of Sweden in my vlookup formula. I am not sure on how combo boxes works, I am reading up on them as I type. This webpage is a good start: https://support.office.com/en-us/article/add-a-list-box-or-combo-box-to-a-worksheet-in-excel-579e1958-f7f6-41ae-ba0c-c83cc6e40878 In this youtube video the index formula is used to find the value of the combo box: https://www.youtube.com/watch?v=VXlKM3YaWag
Sort the list that the combo boxes are populated from. This thread is helpful in sorting on multiple criteria: Excel VBA Multi line sort by multiple criteria in Excel 2016

Thank you for your reply. I want the combo box to have one column. — Waimea
I don't have code on my own but I found this thread mrexcel.com/forum/excel-questions/… Also, this thread contains code fontstuff.com/access/acctut10.htm — Waimea

TinMan TinMan · Accepted Answer · 2018-07-07T09:00:47

Hold down Ctrl + t to convert your list range to a table. This code will work for an ActiveX ComboBox or ListBox. MsFormsListName is the name of the worksheet that has the control. The table can be anywhere in the workbook that call the subroutine.

Sub Test()
    AddTableRowAsListColumn "Table2", "ComboBox1", "Sheet2", 1
    AddTableRowAsListColumn "Table2", "ListBox1", "Sheet2", 1
End Sub

Sub AddTableRowAsListColumn(TableName As String, MsFormsListName As String, MsFormsListWorksheetName As String, RowIndex As Long)
    Dim MsFormsList As OLEObject
    On Error Resume Next
    Set MsFormsList = Worksheets(MsFormsListWorksheetName).OLEObjects(MsFormsListName)
    On Error GoTo 0

    If MsFormsList Is Nothing Then
        MsgBox MsFormsListName & " not found on " & MsFormsListWorksheetName
        Exit Sub
    End If

    MsFormsList.Object.list = WorksheetFunction.Transpose(Range(TableName).Rows(RowIndex).Value)
End Sub

Sub AddTableColumnToList(TableName As String, MsFormsListName As String, MsFormsListWorksheetName As String, ColumnIndex As Long)
    Dim MsFormsList As OLEObject
    On Error Resume Next
    Set MsFormsList = Worksheets(MsFormsListWorksheetName).OLEObjects(MsFormsListName)
    On Error GoTo 0

    If MsFormsList Is Nothing Then
        MsgBox MsFormsListName & " not found on " & MsFormsListWorksheetName
        Exit Sub
    End If

    MsFormsList.Object.list = Range(TableName).Columns(ColumnIndex).Value
End Sub

Excel VBA Replace data validation lists with combo box

1 Answers