Help with cascading/ depandant comboboxes in excel?

1 reply [Last post]
Fri, 12/18/2009 - 06:35
Hattgligism
User offline. Last seen 2 years 35 weeks ago. Offline
Joined: 06/06/2009
Posts: 7

so i have a form in excel that is used to enter data in the excel sheet......so what i want to do is : i have countries list on sheet2.....so in the first combo box i have selected the rowsource as that coutries list ......but here lies the prob on sheet3 , sheet4....and so on i have cities in that particular coutries......so how can i set a vba code which automatically loades the list of cities if the user selects a particular coutry in the first combobox

i knw i can use indirect function if i just make a data validation list....but i dont want to do that because i want it done thru a form .....and the lists may change so i dont want to use the addlist in vba as well ......

so what i want in how to use indirect .....or any other coding for that matter in vba

i will choose best answer immediately and award 5 stars...
pls help

‹ Excel VBA: What is wrong with this code? Need help with my form.? ›
Top
Mon, 01/18/2010 - 18:29
#1
tgkprog
User offline. Last seen 2 years 3 weeks ago. Offline
Joined: 01/18/2010
Posts: 2
you will need VBA code to do
you will need VBA code to do that. how is the data in the other sheets? I assume you have multiple columns - first row with country and then below that city names:
US| UK | India
Washington DC| London|Delhi
LA| Glasgow | Mumbai
NY| Leeds | Bangalore
Philadelphia||Chandigarh
||Calcutta

First figure out which column has the data by comparing the country names

Cities can be 1 to x - the number of rows can be variable, you need a while loop to run thru them.
first clear the combo box of its current entries, then set up a counter and a while loop, add cities to the combo and finally exit the loop when the current cell has no data. start from the first city.
const D1_Offset=2' if data is not from row 1
Sub initCountries()
Dim i As Integer
i = 1
ComboBoxCountries.Clear
Do While Cells(2, i).Value <> ""
ComboBoxCountries.AddItem Cells(2, i).Value
i = i + 1
Loop
End Sub

this code will help you start off. might need to keep an offset of the data is not from a start row when looking for selected country
Top