cascading combo boxes

I am trying to make cascading combo boxes where: the user selects a country in the first combo box and the second combo box lists the cities within the country selected in the first combo box.

When I use data validation using indirect function, my model works perfectly but I really want to do it with combo boxes so that the user can go straight to a city by typing.

Your help will be much appreciated.

Thanks a bunch

Hi Nourie,

Thanks a lot for the effort - it looks very good but the country name shows up in the second combo box (where I only want to see the cities)

Would that be possible to remove it and show only city names?

Thanks

If there are no cities, it would be great if I could see just a blank line (basically nothing).

I am getting a run time error for some values :When I debug, the following line is highlighted in yellow

ComboBox2.List = rngCities.Value

Hey Nourie,

It works well now; however, when I try to copy the two worksheets (sheet1 and countries and cities) to another excel file, I get a run time error 381 again which says "could not set the list property. invalid property array index". When I debug, the following line in the code is highlighted in yellow:

ComboBox1.List = Application.Transpose(arrCountries)

What do you think is the reason? I tried copying the two sheets to a few files and combo boxes worked fine in all except for the file where I need them.

I kept the same sheet names from the file you sent me - and there are no other sheets in the new file with same sheet name. Why do you think it is working in other files?

Could that be because of cell range names? I have range names that are the same as the ones you use.

Indeed, I cleared all range names that are the same as the ones you use. I am still getting a run time error once I click on the first combo box. Your help will be highly appreciated

Can you tell me what potential steps I should be following if I need to copy these two sheets to a different excel file? copying and pasting the two sheets did not work out as I got the error message I told you before

Thanks for the reply, John. I tried copying those sheets to a few different files and it seems like I am getting the error message in one file only. The code works will in all the others...

Here is the line that is highlighted when I debug:
ComboBox1.List = Application.Transpose(arrCountries)

I indeed tried copying destination sheets to the source and it worked.

I am linking the output of the two combo boxes to 2 separate cells in a worksheet. Lets say I choose US from the first combo box and NY from the second combo box - and cell A1 shows US and A2 shows NY. Is it possible to clear the contents of cell A2 and add a text "please choose a city" once the I change the country in the first combo box?