0

I have two combo boxes on an Excel spreadsheet. The contents of combo box 2 depends on the selection in combo box 1.

enter image description here

Here is what I have for the code in combo box 1:

Private Sub cboLine_DropButtonClick()
    Dim item_row, combo_item, list_sheet As Worksheet
    Set list_sheet = Worksheets("Lists")
    Me.cboLine.Clear
    item_row = 1
    Do
        item_row = item_row + 1
        combo_item = Application.WorksheetFunction.HLookup("Lines", list_sheet.Range("A1:Z10400"), item_row, False)
        If Len(combo_item) > 0 Then Me.cboLine.AddItem combo_item
    Loop Until Len(combo_item) = 0
End Sub

And here is what I have in combo box 2:

Private Sub cboMachine_DropButtonClick()
    Dim item_row, combo_item, list_sheet As Worksheet
    Set list_sheet = Worksheets("Lists")
    Me.cboMachine.Clear
    Dim line_name
    line_name = Me.cboLine.Value

    If Len(line_name) = 0 Then
        MsgBox ("Please select Line.")
    Else:
        line_name = line_name & " Machines"
        item_row = 1
        Do
            item_row = item_row + 1
            combo_item = Application.WorksheetFunction.HLookup(line_name, list_sheet.Range("A1:Z10400"), item_row, False)
            If Len(combo_item) > 0 Then Me.cboMachine.AddItem combo_item
        Loop Until Len(combo_item) = 0
    End If 
End Sub

The first combo box shows the right list items but is still blank after the selection. Because of this the second combo box cannot get the right value from the first one.

What is the correct way of initializing combo boxes in VBA? Where should the initialization be done, in combobox_DropButtonClick() or combobox_Change()?

  • Better, use the 'Change' Event with ComboBox. Also, write what Error this code has generated !! – Rajesh S Jan 23 '18 at 7:57
  • You can achieve the same thing without using VBA combo boxes by the use of data validation and named ranges. The only drawback would be the data in combo box 2 would not change without VBA code. – wbeard52 Jan 23 '18 at 15:55
0

Replace the Click Event with ComboBox Change Event, or instead of ComboBox you may use Dependable Drop Down List also. It will perform in the same manner.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.