using two comboxes... to populate a third

[smartphreak]

Hey Guys,

I am using one combobox to populate the second.

as follows:

Private Sub ComboBox_Region_Change()

Select Case Me.ComboBox_Region.Value
Case "NORTH AMERICA"
    Me.ComboBox_Country.RowSource = "NORTHAMERICA"
Case "NORTH AMERICA"
    Me.ComboBox_Country.RowSource = "SOUTHAMERICA"
Case "EUROPE"
    Me.ComboBox_Country.RowSource = "EUROPE"
Case "ASIA"
    Me.ComboBox_Country.RowSource = "ASIA"

End Select
ComboBox_Country.SetFocus

End Sub


Private Sub UserForm_Initialize()
Worksheets("Lists").Range("NORTHAMERICA").Name = "NORTHAMERICA"
Worksheets("Lists").Range("SOUTHAMERICA").Name = "SOUTHAMERICA"
Worksheets("Lists").Range("EUROPE").Name = "EUROPE"
Worksheets("Lists").Range("ASIA").Name = "ASIA"

Worksheets("Lists").Range("REGIONS").Name = "REGIONS"
Me.ComboBox_Region.RowSource = "REGIONS"

End Sub

This works great - only i want to be able to have a third combobox look at the result of combobox_County and populate combobox_location (the 3rd combobox) which is under the named cell USA.

Ie.
Run form
Select "North America" from the combobox_Region
Select "USA" from the combobox_Country
Select a state from the combobox_Location <----- dont know how to populate that

Any ideas?

Thanks a million

 

[Andrew Poulsom]

Welcome to MrExcel.

You would do it in the ComboBox_Country_Change event procedure.

By the way, in your UserForm_Initialize procedure why are you assigning the same names to existing names?

 

[smartphreak]

Hi Andrew,

Thanks - would i do it in the same fashion as above?

I do not know -it is the way i have always done it... making sure that I assign the same name to my VBA as my named ranges.

smartphreak

 

[Andrew Poulsom]

Yes the same.

The RowSource property is a string. Excel will know that it's the name of a range.

 

[smartphreak]

Thank you.