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
Private Sub UserForm_Initialize()
Dim rSides As Range
Dim rCell As Range
Set rSides = Sheet1.Range(“Sides”)
Me.lbxSide.List = rSides.Value
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.