CODEWITHSUNDEEP
excelvba
javad
javad

Reputation: 105

Excel VBA adding values of a column into a Combo box based on another Combo box

I want to add items in column A to combobox1 and then add item in column B to combobox2 based on combobox1 value.

   A   B
1  a  ddd
2  a  fgh
3  a  jcv
4  b  ggh
5  b  ggg
6  b  fff
7  b  hhh

For example
If I choose "b" in combobox1 then these strings should be added to combobox2: ggh, ggg, fff and hhh
My code doesn't work. Thank you in advance.

Private Sub ComboBox1_Change()
With Sheet3.ComboBox1

For Each Cell In Range("A1:A7")
.AddItem Cell.Value
Next

End With
Dim index As Integer
index = ComboBox1.value
Call combo2
End Sub


Private Sub combo2()

For Each Cell In Range("A1:A7")
Select Case index
    Case Is = a
        With ComboBox2

            .AddItem "offset(cell.address,1,1,1,0)"

        End With
    Case Is = b
        With ComboBox2
            .AddItem "offset(cell.address,1,1,1,0)"
        End With

End Select

Next

End Sub

Upvotes: 1

Views: 2068

Answers (1)

Amen Jlili
Amen Jlili

Reputation: 1944

Your code looks really messed up much like your question. I can only try to point some mistakes.

Here's what you need to correct:

  1. Declare Index as public so the other sub can access it. Outside of your first event sub, add the line Public Index As String.
  2. You should use the case select like case "a" and not case Is = a. The Is operator is used to compare object references, meanwhile, you're dealing with a simple datatype comparison.
  3. In your case "a" and case "b" instructions, within yout with statement, use a For Each statement to loop through your range "B" for values. It would look like something like in the code sample below.
  4. In your question, you say:

For example If I choose "b" in combobox1 then these strings should be added to combobox2: ggh, ggg, fff and hhh

However, you're filling your first comobox with values from the column A and I can only assume you meant to say B, otherwise, your select case statement would not work.

Code sample:

For Each Cell in Range("B1:B7")
If Cell.Value = "a" Then
.AddItem Cell.Offset(0,1).Value
End If
Next

Upvotes: 1

Related Questions