How to fill a combo box with choice from another combo box

[Brian]

I am trying to fill combo box with a choice made in another combo box.

I am trying to select a choice from a combo box "Customer_11" and then have
the following choices available in another combo box "State_11". Below is the
code I was trying to use, but not having any luck with getting it to work.


'=====================================================
'CES Customer Information
With Me.Customer_11
.AddItem "Customer 1"
.AddItem "Customer 2"
.AddItem "Customer 3"
.AddItem "Customer 4"
.AddItem "Customer 5"
.AddItem "Customer 6"
.AddItem "Customer 7"
End With
'=====================================================




'************************************************************
'Auto Fill Customer Information
'************************************************************
Private Sub Customer_11_Change()

Select Case Customer_11

Case "Customer 1"
State_11 = "FL"
State_11 = "GA"
State_11 = "AL"
State_11 = "TN"
State_11 = "KY"
State_11 = "NC"
State_11 = "SC"
State_11 = "MS"
State_11 = "LA"

End Select
End Sub

 

[ryguy7272]

I think this is what you want:
http://www.contextures.com/xlDataval02.html

http://www.contextures.com/xlDataVal13.html

 

[Brian]

That is for Combo Boxes on a worksheet. I am trying to do this on a User Form.

 

[Jacob Skaria]

Brian

You need to Add the items to the State combo.as below..

Private Sub CommandButton1_Click()
With Me.customer_11
.AddItem "Customer 1"
.AddItem "Customer 2"
.AddItem "Customer 3"
.AddItem "Customer 4"
.AddItem "Customer 5"
.AddItem "Customer 6"
.AddItem "Customer 7"
End With

End Sub
Private Sub Customer_11_Change()

state_11.Clear
Select Case customer_11
Case "Customer 1"
state_11.AddItem "FL"
state_11.AddItem "GA"
state_11.AddItem "AL"
state_11.AddItem "TN"
state_11.AddItem "KY"
state_11.AddItem "NC"
state_11.AddItem "SC"
state_11.AddItem "MS"
state_11.AddItem "LA"
End Select
End Sub

 

[Brian]

I pasted the code exactly as you have it. but I can't get anything to show up
in the 2nd Combo Box "State_11".

Everything works great on the 1st Combo Box "Customer_11", but I can't get
anything to show up in the 2nd Combo Box "State_11". What did I do wrong?

 

[Brian]

Private Sub UserForm_Initialize()
With Me.customer_11
.AddItem "Customer 1"
.AddItem "Customer 2"
.AddItem "Customer 3"
.AddItem "Customer 4"
.AddItem "Customer 5"
.AddItem "Customer 6"
.AddItem "Customer 7"
End With

End Sub
----------------------------------------------------------------------------

Private Sub Customer_11_Change()

state_11.Clear
Select Case customer_11
Case "Customer 1"
state_11.AddItem "FL"
state_11.AddItem "GA"
state_11.AddItem "AL"
state_11.AddItem "TN"
state_11.AddItem "KY"
state_11.AddItem "NC"
state_11.AddItem "SC"
state_11.AddItem "MS"
state_11.AddItem "LA"
End Select
End Sub

 

[Jacob Skaria]

Instead of updating the 1st combo during Form initialize; I used a command
button click event...

 

[ozgrid.com]

http://www.ozgrid.com/VBA/dependent-combobox.htm