LinkBack URL
About LinkBacks
Hey there.
This is slightly long, although only the first part is really my question - the rest is my reasoning for different approaches which don't seem like they'll work... so that's not as important = )
So, I have some comboboxes I plan to use for state, city, and local address on a userform. I want to populate them in such a way that each higher-level category restricts the options for each lower-level entry. Example: If someone chooses the state of CA in the first box, only cities in CA are showing up in the "city" combobox. I know this is fairly straightforward, but how I am trying to implement it is not.
First off, the data I am using has already been set up in a special worksheet. The general format is this:
States are listed across the top in the first row, one state per column.
Cities are listed in the columns beneath the state they belong to. The last entry in each city list is the word "Other" (which can be selected if the city the user wants is not already in the list). Also at the end of each Cities list (after Other) is a blank cell (which might be helpful to use later? anyway...). Example:
Alaska | Alabama | ...
Anchorage | Birmingham
Other | Mobile
| Other
Local addresses are organized in a somewhat similar way below this...
I have the State combobox populating well, but I am having trouble figuring out how to get the Cities combobox working. Something making this more challenging is the fact that the list of Cities is supposed to be dynamic, so the list may grow (but probably not shrink).
Does anyone have any suggestions on getting this working? Here are some ideas I've had, but that don't seem like they'll work out.
~~~~~~~~~~~~~~
Scenario 1:
Use a foreach statement and the value of the state combobox to cycle through the range of states in the States row. Then take that column value and display in the Cities combobox everything in a range from the column below the State until reaching a cell with the value "Other." I can't figure out a way to get the range to stop at a (dynamically ending) cell once it reaches a value of "Other" however. I thought I could use a MATCH, but I think I'd need the row position of Other to define the end of range input for that (the "Lookup_array" value). So I can't find something if I need that something to find it!!
Anyway, in pseudo-code something like this:
Foreach (cell) in (Range of States)
if comboboxState.Value = cell.value
statecolumn = (column value of (the current) cell)
else
endif
Next cell
Then something like:
(varEndCityRange) = MATCH("Other", (start range, end range), 0)
Foreach (cell) In .Range(.Cells(3, statecolumn), .Cells(varEndCityRange, statecolumn))
comboboxCity.AddItem (cell).Value
Next (cell)
Again, I don't think this will work because I can't figure out how to define the ranges in MATCH without already knowing the info I'm looking for (!!)
~~~~~~~~~~~~~
Scenario 2:
Once I know the column value for state, the cities start two rows down (so that's a fixed position). I was thinking of using OFFSET to somehow ID the range and spit it into the Cities box, but again I don't know how I could query down the list until I hit "Other" and then stop.
~~~~~~~~~~~~~~~
Scenario 3:
I could throw the entire range into an array, query for Other, find its cell position, and try to work from there. I get kind of lost from there though... that seems unnecessarily complex - unless I am missing something (very possible).
~~~~~~~~~~~~~~~~~
Scenario 4:
Make a variable Named Range for each list of cities. I think once this is done all I'd need to do is assign the combobox value to the named range. BIG downside on this is that I think this means I'd have to make a separate range for every state (dozens) AND that I'd probably need to use a similar strategy when I move on to populate the Local Address combobox (so dozens x about another dozen, NOT a great prospect).
And insight into this brain twister would be very well appreciated!!
Thanks = )
Register To Reply
Bookmarks