Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Listbox value depending on other listbox value
Hi,
I use in 1 form 2 listboxes, one to select a country, and one to select a city. If the user selects a county, automaticly from the list a default city shoudl be made visible in the second listbox. At this moment the default is not set, possible because the value of the first listbox is not yet set in the worksheet? How to make this happen? regards Gerard |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Listbox value depending on other listbox value
Indirect() is the function
example 4 tables. Table 1, range name COUNTRIES, list is ENGLAND, FRANCE , USA Table 2, range name ENGLAND, list is London, Norwich, Liverpool, Manchester Table 3, range name FRANCE, list is Paris, Rheims, Lyon Table 4, range name USA, list is New York, Los Angeles, Boston, Orlando in a cell, named SelectedCountry, add data validation, select List and =Countries in another cell, SelectedCity, add data validation, choose list and the source is =INDIRECT(SelectedCountry) add a CHANGE event to the sheet, that clears SelectedCity if the changed cell was SelectedCountry Hope this gives you an idea http://www.xl-expert.com/IndirectValidation.htm "noord453" wrote in message ... Hi, I use in 1 form 2 listboxes, one to select a country, and one to select a city. If the user selects a county, automaticly from the list a default city shoudl be made visible in the second listbox. At this moment the default is not set, possible because the value of the first listbox is not yet set in the worksheet? How to make this happen? regards Gerard |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Listbox value depending on other listbox value
You need to set up a list of towns for each country. Name the list of towns
after the country they relate to. The list of countries you could name Countries. For each country you would then have a seperate list. Say you have Argentina, Equador, Poland, Russia, South Africa. The source for listbox1 would then be Countries. In your second listbox, indicate the source as =INDIRECT(Listbox1). In this way, it would read the list from the range name pertaining to that country. -- HTH Kassie Replace xxx with hotmail "noord453" wrote: Hi, I use in 1 form 2 listboxes, one to select a country, and one to select a city. If the user selects a county, automaticly from the list a default city shoudl be made visible in the second listbox. At this moment the default is not set, possible because the value of the first listbox is not yet set in the worksheet? How to make this happen? regards Gerard |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Listbox value depending on other listbox value
Patrick,
Thanks for your answer. Please let me explain more my challange. In a form, I want to select a county, this selection is stored in sheet1 cell C3, this cell is named country. In sheet2 cell C5 a vlookup is done to find a match in a table which looks like this: =IF(country<"";VLOOKUP(country;'Parameters - lists'!G2:I200;3;FALSE);"") Where Parameters lists, is a sheet, in which I have stored all the lists. Regards Gerard Country Customer Centre Netherlands Amsterdam U.K. Bristol France Amsterdam Belgium Bristol Germany Bonn In the same form the second listbox must show, the corresponding customer centre, based on the value of sheet2 cell C5 So I am looking for the command in VB to make this happen because now because the form is not completed yet, the county value is not set in sheet1 C3, or I am looking for some programming in VB to do the VLOOKUP. The VLOOKUP command used in Sheet2 C5 is as follows: "Patrick Molloy" wrote: Indirect() is the function example 4 tables. Table 1, range name COUNTRIES, list is ENGLAND, FRANCE , USA Table 2, range name ENGLAND, list is London, Norwich, Liverpool, Manchester Table 3, range name FRANCE, list is Paris, Rheims, Lyon Table 4, range name USA, list is New York, Los Angeles, Boston, Orlando in a cell, named SelectedCountry, add data validation, select List and =Countries in another cell, SelectedCity, add data validation, choose list and the source is =INDIRECT(SelectedCountry) add a CHANGE event to the sheet, that clears SelectedCity if the changed cell was SelectedCountry Hope this gives you an idea http://www.xl-expert.com/IndirectValidation.htm "noord453" wrote in message ... Hi, I use in 1 form 2 listboxes, one to select a country, and one to select a city. If the user selects a county, automaticly from the list a default city shoudl be made visible in the second listbox. At this moment the default is not set, possible because the value of the first listbox is not yet set in the worksheet? How to make this happen? regards Gerard |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
userform listbox cannot get listbox.value to transfer back to main sub | Excel Programming | |||
VBA: Creating listbox similar to the one in Pivot table (Listbox+Checkbox) | Excel Programming | |||
listbox.value not equal to listbox.list(listbox.listindex,0) | Excel Programming | |||
Filling a listbox depending on font index | Excel Programming | |||
Is refreshing listbox rowsource in listbox click event possible? | Excel Programming |