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