Basing One Listbox on Another

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:

example of userform with two listboxes

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.

Excel worksheet showing range names

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.

Private Sub lbxSide_Change()
   
    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

example code from microsoft with bad formatting

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.

Posted in Uncategorized

3 thoughts on “Basing One Listbox on Another

  1. “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

  2. 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?


Posting code? Use <pre> tags for VBA and <code> tags for inline.

Leave a Reply

Your email address will not be published.