Several dynamic listboxes

[Sql7user]

Hi,

In Excel I want to put 2 or more listbox/combox that are dynamic and linked. Depending of the choice I make in the first i get choices in listbox 2 etc. For example, I choose countries in listbox 1, get the cities in number 2 and in number 3 I get the streets.

I have no idea how to make this so plz explain in detail.
Thanks in advance!

Sql7user

 

[xlbo]

You will need a list set up like this:

Country1 City1 Street1
Country1 City1 Street2
Country1 City2 Street3
Country1 City2 Street4
Country2 City3 Street5
etc etc
Name the countries column "Countries", the cities column "Cities" and the streets column "streets"

You will also need 3 combo boxes:
cbCountries
cbCities
cbStreets

On form activate

cbCountries.clear

For each c in range("Countries&quot
if c.text <> c.offset(-1,0).text then c.additem (c.text)
next

In cbCountries change event

with range(&quot;Countries&quot
set fCell = .find(cbCountries.text, lookin:=xlvalues, lookat:= xlwhole)
if not fCell is nothing then
firstAddress = c.Address
Do
cbCities.additem (fCell.offset(0,1).text)
Set fCell = .FindNext(fCell)
Loop While Not fCell Is Nothing And fCell.Address <> firstAddress
Else
msgbox &quot;Country Does not exist you fool&quot;
End If
end with

In cbCities change event:

with range(&quot;Cities&quot
set fCell = .find(cbCities.text, lookin:=xlvalues, lookat:= xlwhole)
if not fCell is nothing then
firstAddress = c.Address
Do
cbStreets.additem (fCell.offset(0,1).text)
Set fCell = .FindNext(fCell)
Loop While Not fCell Is Nothing And fCell.Address <> firstAddress
Else
msgbox &quot;City Does not exist you fool&quot;
End If
end with


Rgds, Geoff
Quantum materiae materietur marmota monax si marmota monax materiam possit materiari?
Want the best answers to your questions ? faq222-2244