Userform Dependent Listboxes

Posted on by Dick Kusleika

I often have a requirement to display Parent-Child-Grandchild relationships in a userform. That usually takes the form of listboxes where lesser listboxes are populated based on selections of greater listboxes. The other day I created a generic one. I was thinking that it would be nice to plop this into a project and replace some variable names. Maybe it would eliminate some of the drudgery of creating userforms. I question whether it will be better than just starting from scratch, but time will tell. In the mean time, here’s what I did.

Sub Main()

Dim clsParents As CParents
Dim ufRelations As URelations

Set clsParents = New CParents
clsParents.FillFromRange Sheet1.Range("A2:B17")

Set ufRelations = New URelations
Set ufRelations.Parents = clsParents

ufRelations.Initialize
ufRelations.Show

Unload ufRelations
Set ufRelations = Nothing

End Sub

The top level class, CParents, is held in a variable and passed to the userform via a Public Property. Every CParent, CChild, and CGrandchild can be accessed through that one top level collection class. The Intialize method calls the FillParents procedure and does nothing else. There’s a FillParents, a FillChildren, and a FillGrandchildren procedure that populate the respective listboxes. They all follow pretty much the same pattern.

Private Sub FillParents()

Me.lbxParents.List = Me.Parents.List

If Me.lbxParents.ListCount > 0 Then
Me.lbxParents.ListIndex = 0
End If

End Sub

The List property of the listbox is assigned the List property of the class, which returns a zero based array specifically to fill the listbox. Then, as long as there’s something there, the first parent is selected (ListIndex = 0). That selection triggers the lbxParents_Change event.

Private Sub lbxParents_Change()

If Me.lbxParents.ListIndex >= 0 Then
Set Me.ActiveParent = Me.Parents.ParentByDescription(Me.lbxParents.Value)
Else
Set Me.ActiveParent = Nothing
End If

FillChildren

End Sub

The userform class has two properties, ActiveParent and ActiveChild, that should hold a reference to the class instance matching what’s selected in the listbox. In this code, if something is selected, ActiveParent is assigned, otherwise it’s set to Nothing. Then FillChildren is called. Notice my use of Me.lbxParents.Value as I have something to say about that later.

Private Sub FillChildren()

Me.lbxChildren.Clear

If Not Me.ActiveParent Is Nothing Then
If Me.ActiveParent.Children.Count > 0 Then
Me.lbxChildren.List = Me.ActiveParent.Children.List
Me.lbxChildren.ListIndex = 0
End If
End If

End Sub

I’m sure you can see the pattern: Set the List property of the control to the List property then select the first one in the list. That triggers a Change event that calls the next level down. I like for something to always be selected in a listbox. That is, I never want to listbox with a ListIndex of -1. To my amazement, there are people who don’t agree with me. They prefer a Null state and I prefer to limit the Null states as much as possible.

The obvious reason is that it simplifies the code. If you can count on a certain state, it means less checking down the line. Another reason, which may be the same reason, is that the code that populates the listboxes initially is the same code that accounts for changes to the listbox. I know the code works when I initialize because I’m exercising it.

I’m not going to show you the rest of the code because it’s not much different than what I’ve already shown. You can see it all in the download at the bottom of this post if you like. But I do want to discuss one other issue: Using the Value property of the lisbox. Here’s the change event for the Children listbox

Private Sub lbxChildren_Change()

If Me.lbxChildren.ListIndex >= 0 Then
Set Me.ActiveChild = Me.ActiveParent.Children.ChildByDescription(Me.lbxChildren.List(Me.lbxChildren.ListIndex))
Else
Set Me.ActiveChild = Nothing
End If

FillGrandchildren

End Sub

In the change event for the parent listbox, I used the Value property to locate the selected parent. Here I’m using something different. The Value property of lbxParents worked every time I ran the code. The Value property of lbxChildren worked about 25% of the time. I thought I knew everything there was to know about the Value property, but clearly I don’t. I understood that Value would return the text in the BoundColumn of the ListIndex row. In 75% of the cases, it was returning an empty string. The errors only occurred in the Initialize procedure. Once the form was up and running, it never failed. When I put a break point in the code to debug it, it worked more often (the uncertainty principle in action). To fix the problem, I used the construct above. Using the List(ListIndex) method failed 0% of the time. I don’t have an explanation, but I’ll be forever nervous about using Value.

If I really want this to be a drop-in module, I need to make one major change. I need to make all of the listboxes with a hidden first column for the ID of the object. In this example, I use the Description property to find the correct object instance but I wouldn’t do that in real life if I didn’t have to. I have the feeling I don’t “reuse” code as much as other people. I’m happy to use some APIs or error handling code by dropping it in. But most of the code I write from scratch – until it doesn’t work, then I go see how I did it before. There are some advantages to writing from scratch, such as doing it better than I did it before. And of course there are advantages to reusing, such as reliable, tested code. Hooking up controls on a userform has to be one of my least favorite activities, so I will be happy if I can find some reusable code framework to minimize it.

You can download ParentChildUserform.zip

P.S. I started using the Public folder of my Dropbox account to host downloads. It’s easier than uploading via ftp and creating a link. I can’t think of any downside to that. A little less control I guess.

19 thoughts on “Userform Dependent Listboxes”

  2. Great post DK and very timely. I was actually wondering how best to create an tiered object structure, and your post provides a great example. Just curious about the purpose of the CopyMemory procedure, which does not seem to be executed by the ObjFromPtr function. Would be great to understand its purpose.

  3. re: “The Value property of lbxChildren worked about 25% of the time”
    Excel GUI forms controls have always been quirky IMHO.
    Nice job Dick….this is very timely for me…I’m working on a 3 level hierarchy maintenance form after abandoning a “n-level” hierarchy approach.

  5. I learned some time ago never to use the value property of a list box. I have not been able to establish why it is unreliable but there is no doubt that it is. The trouble is testing doesn’t really help as it will probably not show the error.
    So always use: Result = frmTest.lstTest.List(frmTest.lstTest.Listindex, 0) – O.K. you can miss out the ,0 for a single column listbox but using the full version is totally reliable.

  6. GordonK: When you say “executed” I assume you mean that you can’t step through it. It’s called from ObjFromPtr twice, but because it’s an API the code execution is in the kernel and invisible to you and me.

  8. Yes, I agree about the Null state or Listindex=-1

    The question, of course, is defining what a valid default should be: in the case of a cascading filter, the default is ‘Show ALL’ or a dummy entry labelled ‘(No matching rows)’.

    That’s a good user interface, because the users always see what they have done and what they are expected to do next; but there’s a significant overhead in the underlying logic, because you’ve got to hard code special handling for the two magic values ‘ALL’ and ‘No matching’.

    It’s also worth asking whether a Treeview might be a better way for the users to visualise their selection: it is, after all, hierarchical data in a tree structure.

  10. Hi Dick,

    Great Solution …
    Is there a simple way to have just an additional level ?
    Since just found out a need for Grand-Grandchildren …
    Thanks

  11. Thanks Dick for a contributing this code. I am having difficulty running this on Office/Excel 2010 64 bit. The error message points to : Private Declare Sub CopyMemory Lib “kernel32” Alias “RtlMoveMemory” _
    (dest As Any, Source As Any, ByVal bytes As Long)

    I have tried and tried to modify the code to be compatible, but I just dont know how to use the PtrSafe attribute. I would be most grateful if you could advise.

    A second question- How can you ensure that the Child & Grandchild listboxes are populated when the form is initially opened? When I run it, these boxes are blank and are only populated after a ‘Parent’ is selected. Is it to do with the ListIndex?

    regards
    Richard

  12. Richard: The Windows API calls are different in 64-bit. See JKP’s site for the proper formats http://www.jkp-ads.com/articles/apideclarations.asp

    The Child and Grandchild boxes should fill right away as long as there is at least one parent. Initialize calls FillParents. FillParents sets the ListIndex to zero. Doing that fires lbxParents_Change, which calls FillChildren. Setting the ListIndex to zero is the same as selecting. I’m not sure what could be happening with yours that it’s not populating the other two boxes. You could put some Stops in your code and F8 through it to see what’s getting called and what’s not. Or send me your workbook and I’ll look at it. dick@kusleika.com

  13. Thank you so much. But can i go a little further and ask you a question. If list box has a no. of criteria for eg. in my case I have different hobbies in the first list box. I would like to see all the students who have registered for this. However, there might be an overlap and I would want the names only to repeat this once. I have this information listed in a database from which I would want this to be extracted on to the list box.

    Is this possible? If so could you please help me tweak the code so that I can make multiple selections in listbox 1in such a way that the values selected in list box two has all the values applicable for the selections made (listbox) but removes all duplicates?

    Thank you so much in advance!!!

  17. Hi,

    im trying to concatenate parent and child trying to get Grand children. but, im unable to perform the edit. please help

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

Leave a Reply

Your email address will not be published.