Daily Dose of Excel

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.

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.

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.

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.

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

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.