Similar to using Conditional Data Validation, you may have a need to change the contents of one listbox based on what the user selects in another listbox. Assume you have two listboxes on a userform: one for sides and one for players. When the user selects a side, the list of players changes. It will look like this:
Start with three named ranges. The first name is “Sides” and covers A1:A2 (which contain Chelsea and ManU). The other two range names are “Chelsea” and “ManU” and cover the lists of players in columns B and C. It’s important for this example that the named ranges for the players exactly match the values in A1 and A2. You can set up a situation where they don’t match, but it takes a bit of extra programming.
The code behind the userform is pretty simple. In the Initialize event, you load up the Sides listbox. Then, whenever the user changes the value of that listbox, you load up the Players listbox.
Dim rPlayer As Range
Set rPlayer = Sheet1.Range(Me.lbxSide.Value)
Me.lbxPlayer.List = rPlayer.Value
End Sub
Private Sub UserForm_Initialize()
Dim rSides As Range
Dim rCell As Range
Set rSides = Sheet1.Range(“Sides”)
Me.lbxSide.List = rSides.Value
End Sub
Microsoft’s KB article 161518 discusses this very issue. I have two problems with their solutions: First, it appears they use the RowSource property. I never use this property, so if you’re like me their solution won’t be optimal. I don’t have anything against RowSource, I just like the flexibility that List, AddItem, and RemoveItem afford. Second, the code sample they provide looks like this
when viewed in FireFox. Well, the code on my site can be viewed from any browser. You still can’t copy and paste it if it contains quotes, but at least you can read it.
“You can set up a situation where they don’t match, but it takes a bit of extra programming.”
I need to do it so they don’t match, could you possible provide an example or explain how to do that?
Thanks for your info
Scott
A problem with this:
Me.lbxSide.List = rSides.Value
is that if you only have one cell in the “Sides” range, you get the “Could not set the List property. Invalid property array index” error.
That’s because the .list property expects a variant array, which you don’t get when the range evaluates to only one item.
I guess you have to check the size of “Sides” first. Anyone know a more elegant solution to this?
DM: Strangely, I never use that method. I don’t know why I did for this post. I use AddItem, although I don’t consider it more elegant, I just prefer it.
http://www.dailydoseofexcel.com/archives/2004/05/07/populating-one-column-listboxcombobox/
http://www.dailydoseofexcel.com/archives/2004/05/10/populating-multi-column-listboxcombobox/