combobox events and variable setting

[rokuk]

I have two comboboxes in a userform. The first ("boxState") has a .RowSource set to a range of states, and the second ("boxCity") has a .RowSource set to a named range of cities (which is dependent on the value chosen in boxState).

ie.

Please Login or Register  to view this content.

So if I choose "NY" in boxState, I also have a named range called "NY" that contains all the cities I have listed in NY. This inserts the correct cities into boxCity, and works fine.


My issue is with getting boxCity to refresh when I make a new selection in boxState. Right now, if I already have an entry in boxCity, and choose a new value in boxState, boxCity's list remains the same. I've tried setting in boxState_Change():

Please Login or Register  to view this content.

but this doesn't modify boxCity's list. I also found I can't use boxCity.Clear because the .Clear method can't be used when you are assigning set ranges (apparently, you'd need to be using AddItem instead).


Any thoughts on getting this sorted out? I'm not sure why the first approach isn't working... is it not resetting boxState.Value in the boxState_Change() method when I change the state selected there and only storing the original choice? Is there a way to force a refresh to the new selection?

[Norie]

Why not try setting the ListIndex to -1.

PS I'm not sure what you mean about Clear.

That's used to, well, clear a combobox of all items.

[rokuk]

well that's what I was expecting to do, I believe:

If boxCity has a list of items, I'd want to clear it and then reset it to the new boxState.Value (instead of just keeping the list based on the original boxState value).

Does that make sense? I'm not looking to navigate the current list for boxCity, I'm looking to assign it an entirely different list, if boxState changes. I don't want cities in Alaska showing up if the user first selects Alaska, and then changes their mind and selects Arkansas! (they should then get Arkansas cities... that's the behaviour I'm trying to get working, right now it would stay the list of Alaska cities even if they chose another state).


I'll look into playing with the index though, thanks

[BigBas]

Could you possibly post a .zip sample of your workbook so we can check it out?

[rokuk]

I looked into getting the relevant pieces separate, but it's a pretty big workbook. But here is (I think all of) the relevant code (just):

Please Login or Register  to view this content.

Please Login or Register  to view this content.

The data is set up, so that all city names for a state are in a range NAMED with the state. So all California cities are in a range -called- "California." This makes populating boxCity as easy as:

Please Login or Register  to view this content.

This is current behaviour:

1. I choose "California" in boxState.
~ displayed in boxState: California
~ displayed in boxCity : <blank>

2. boxCity is populated with a range named "California" - all the cities in California
~ displayed in boxState: California
~ displayed in boxCity : <blank>, but on clicking the drop-down, all cities in California

3. I choose a California city (now boxCity combobox is displaying this city)
~ displayed in boxState: California
~ displayed in boxCity : San Francisco (for example)

4. I choose Florida in boxState
~ displayed in boxState: Florida
~ displayed in boxCity : San Francisco (should be empty!!), but on clicking the drop-down, all cities in Florida

5. boxCity REMAINS displaying the chosen California city
~ displayed in boxState: California
~ displayed in boxCity : San Francisco (user can currently submit userform with a non-matching city / state combo!!)


This would allow a user to submit a record with a city that doesn't exist in the state they chose! I don't how to clear the previously chosen value being displayed in the boxCity combobox.


The following does not work:

Please Login or Register  to view this content.

How can I clear the data currently entered in boxCity when I choose another Value in boxState?

[rokuk]

I figured it out.

I needed to set the .Text property equal to "" to set it blank after boxState_Change() fired. I assumed that I'd need to play with the Rowsource, and even tried setting that = "", but it (obviously) didn't work as I intended. I finally came across a mention of using .Text and I realized that's the same property that I could probably use. This is working for me now:

Please Login or Register  to view this content.

Thanks for all the suggestions!