Toggle Between RowSource Items And List Items In A ComboBox Based On Criteria

[RaghavendraPrabhu]

Hi All,
I have a small problem. Attached a small spreadsheet.

I have a form with two combo boxes. The row sources are “Languages” and “Levels”

Some of the languages have a “*” on the right of the string.

When I select a language, the second column is also populated.

When I select languages with “*” at the right of the string, I want only "Lower" is the only level can be selected.

Private Sub CommandButton1_Click()
Dim eRow As Long
    eRow = Sheets("Sheet2").Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
    Sheets("Sheet2").Activate  
    Cells(eRow, 1) = ComboBox1.Text
    Cells(eRow, 2) = ComboBox1.Column(1)
    If Right(ComboBox2.Text, 1) = "*" Then
        ComboBox2.RowSource = Sheets("Sheet2").Range("E2")
    Else
        ComboBox2.RowSource = "Levels"
    End If
    Cells(eRow, 3) = ComboBox2.Text
    ComboBox1.Text = ""
    ComboBox2.Text = "”
End Sub

Any help with editing the code will be helpful.

Thank you

Raghavendra

[Excel Fox]

One way to do that is given below. Just replace your existing code with it.

Private Sub ComboBox1_Change()
    
    With ComboBox2
        If Right(ComboBox1.Text, 1) = "*" Then
            .RowSource = ""
            .List = Array("Lower")
            .Value = "Lower"
        Else
            .RowSource = "Levels"
            .ListIndex = -1
        End If
    End With
    
End Sub

Private Sub CommandButton1_Click()
    
    Dim eRow As Long
    
    With Sheets("Sheet2")
        eRow = .Cells(.Rows.Count, 1).End(xlUp).Offset(1, 0).Row
        .Cells(eRow, 1).Resize(, 3).Value = Array(ComboBox1.Text, ComboBox1.Column(1), ComboBox2.Text)
        .Activate
    End With
    
End Sub

Private Sub CommandButton2_Click()
    Application.Quit
End Sub