CODEWITHSUNDEEP
excelvbacomboboxuserform
Geo Koro
Geo Koro

Reputation: 75

Excel VBA - UserForm Combobox with If Statement

I've created an Userform. The code is not completed yet, but trying to have conditions on the combobox. Here is the code:

Private Sub UserForm_Initialize()

    With SupText
        .AddItem "Sup1"
        .AddItem "Sup2"
        .AddItem "Sup3"
    End With
    
    With ProdText
        .AddItem "Prod1"
        .AddItem "Prod2"
        .AddItem "Prod3"
        .AddItem "Prod4"
        .AddItem "Prod5"
    End With
    
    With UnitText
        .AddItem "kL"
        .AddItem "T"
    End With
    
    With StaText
        .AddItem "In Progress"
        .AddItem "Awaiting"
        .AddItem ""
    End With
    
    With ProLText
        .AddItem "1"
        .AddItem "4"
        .AddItem "1&4"
        .AddItem "2"
        .AddItem "3"
        .AddItem "2&3"
        .AddItem "WOPL"
        .AddItem "BOPL"
        .AddItem "Industry Line"
    End With
    
    End Sub

So, what I'm trying to do is to ProLText combobox to populate the list according to the SupText. When I use an if statement it doesn't work. For instance, if the choose the Sup1 from SupText Combobox I would like to give only the first 6 option of the ProLText combobox, the Sup2 gives the next 2 and Sup3 the last item. But for some reason, it gives result on for the else statement and not for if or else if.

Any ideas why it doesn't work?

Regards.

Upvotes: 0

Views: 348

Answers (1)

Siddharth Rout
Siddharth Rout

Reputation: 149297

  1. Change the .Style of the comboboxes to fmStyleDropDownList
  2. Do not add items to ProLText in the UserForm_Initialize()
  3. In SupText_Click() event clear the ProLText and re-add relevant items.

CODE

Private Sub SupText_Click()
    If SupText.ListIndex = -1 Then Exit Sub
    
    ProLText.Clear
    
    Select Case SupText.Text
    Case "Sup1"
        With ProLText
            .AddItem "1"
            .AddItem "4"
            .AddItem "1&4"
            .AddItem "2"
            .AddItem "3"
            .AddItem "2&3"
        End With
    Case "Sup2"
        With ProLText
            .AddItem "WOPL"
            .AddItem "BOPL"
        End With
    Case "Sup3"
        With ProLText
            .AddItem "Industry Line"
        End With
    End Select
End Sub

Or a shorter version

Private Sub SupText_Click()
    If SupText.ListIndex = -1 Then Exit Sub
        
    With ProLText
        .Clear
        Select Case SupText.Text
            Case "Sup1"
                .AddItem "1"
                .AddItem "4"
                .AddItem "1&4"
                .AddItem "2"
                .AddItem "3"
                .AddItem "2&3"
            Case "Sup2"
                .AddItem "WOPL"
                .AddItem "BOPL"
            Case "Sup3"
                .AddItem "Industry Line"
        End Select
    End With
End Sub

Upvotes: 1

Related Questions