CODEWITHSUNDEEP
vbaexcel-2007
Stravan A
Stravan A

Reputation: 85

How can I control a ComboBox after another?

The code shows three ComboBox in a UserForm. Combobox2 pick four values ​​1, 2, 3, 4 from the sheet ("Data") and Combobox3 pick 6 values ​​(A, B, C, D, E, F) from the same sheet. What should I do if I want to control Combobox3 after the value is chosen in Combobox2, such as If I choose 1 I want to just A, B, C should be in Combobox3, or if I choose 2 then only D, E found in Combobox3, etc.?

Thanks in advance for any suggestions!

    Private Sub RapportFix_Initialize()
Dim ComboItems As Variant, i As Integer
Dim val As String

    With RapportFix.ComboBox2 'Provtyp
    .Clear ' remove existing entries from the listbox
    ComboItems = Worksheets("Indata").Range("C5:C8").Value
    ComboItems = Application.WorksheetFunction.Transpose(ComboItems)
    ' convert values to a vertical array
    For i = 1 To UBound(ComboItems)
        .AddItem ComboItems(i) ' populate the combobox
    Next i
   .ListIndex = -1 ' no items selected, set to 0 to select the first item
End With
With RapportFix.ComboBox3 'Kursplan
    .Clear ' remove existing entries from the listbox
    'Set sKursplanemoment = ComboBox3.Value
    ComboItems = Worksheets("Indata").Range("M5:M10").Value
    ComboItems = Application.WorksheetFunction.Transpose(ComboItems)
    ' convert values to a vertical array
    For i = 1 To UBound(ComboItems)
        .AddItem ComboItems(i) ' populate the combobox
    Next i
    .ListIndex = -1 ' no items selected, set to 0 to select the first item
 End With

 With RapportFix.ComboBox4 'Annat arbete
    .Clear ' remove existing entries from the listbox
    ComboItems = Worksheets("Indata").Range("E5:E8").Value
    ComboItems = Application.WorksheetFunction.Transpose(ComboItems)
    ' convert values to a vertical array
    For i = 1 To UBound(ComboItems)
        .AddItem ComboItems(i) ' populate the combobox
    Next i
    .ListIndex = -1 ' no items selected, set to 0 to select the first item
End With
RapportFix.Show
End Sub

Upvotes: 0

Views: 3726

Answers (1)

Fionnuala
Fionnuala

Reputation: 91346

Each user form control has a number of events associated with it. The change event suggested by Cody is particularly easy to find as all you have to do is right-click the control in design view and choose "View Code", a sub outline will then be filled in.

enter image description here

As you can see above, there are two dropdown lists above the code page, one lists the controls and objects available and one lists the events. Choose a control and an event to get the outline sub for that event.

In this particular case, you need to use the change event of the controlling combo to first clear and then repopulate the dependent combo, just as you have done above. These are called cascading combos.

Private Sub ComboBox2_Change()
    ''Populate combo 3
End Sub

The only difference is you need to check that the list contains only those items that should appear for each value of the controlling combo. I would be inclined to do this with a list on a worksheet that I could easily control:

Item2 Item3
1       a
1       b
2       d
2       c
3       a
3       e

However, this does not seem possible for you, so you may wish to use Select Case

Private Sub ComboBox2_Change()
    Select Case ComboBox2
        Case "1", "2", "3"
            ''Add items x,y,z to combobox3
        Case "a", "b", "c"
            ''Add items m,n,o to combobox3
        Case Else
            ''Whatever
    End Select
End Sub

Upvotes: 1

Related Questions