In a previous post (this one) we described how to create cascading combo boxes in Excel using data validation. You can achieve the same functionality in your Userforms in VBA. The advantage is that you can do the programming and the end user can create the various lists and maintain those lists on their worksheets. That is the combination we are looking for. In this post I will describe one of the many ways this can be accomplished. Here is how this can be done.
Creating the lists
First we start of by creating the various list options. The first list is the application list. Here we write down the Office apps which we can choose from. The depending lists should only display the options belonging to the specific office application. To make it clear I colored the various lists. See picture below. When we choose Excel from the first combo box we only want to see the green options which belong to excel. When we choose one of the options from the second list we only want to see those options related to that list option. In the example we choose “Functions” so we only want to see the options belonging to “Functions” as displayed in the light green list. If we choose another option from the first list we only want to see the colored option related to that color or choice made from the previous list. So if you want to try this then recreate the list as shown in the picture below. (click on the picture to enlarge)
Naming the lists
The next important step is to “name” the lists you have just created. Naming list is a good habit because it does not matter where you move the list the name will always point to the correct list. We will name the first list we have created “MOS_Apps”. The easiest way to do this is by selecting the list you have created and then click the name box once. So select “A1:A5” and typ in the name “MOS_Apps” and press Enter. Now we have to name all the other lists as well. For ease of use I have named the second lists to their counterpart application name. So do the following:
Select “C1:C5” – click the name box and type Excel – press Enter
Select “E1:E5” – click the name box and type Access – press Enter
Select “G1:G5” – click the name box and type Word – press Enter
Select “I1:I5” – click the name box and type OneNote – press Enter
Select “K1:K5” – click the name box and type PowerPoint – press Enter
Select “C8:C10”– click the name box and type Functions – press Enter
Select “E8:E10” – click the name box and type Tables – press Enter
Select “G8:G10” – click the name box and type SpellCheck – press Enter
Select “I8:I10” – click the name box and type Section – press Enter
Select “K8:K10” – click the name box and type Slide – press Enter
To keep the example simple we only took the first options from the second lists. This means that if you choose Excel and in the second combo choose Function you will see the options belonging to Functions. If you want to extend this with all the other options from the second list then you create a list with options belonging to “sheets” and name that list “Sheets” and so on for every other option.
Creating the Userform
With all the lists created and all the lists “named” we can created our userform with the combo boxes. So open the VBA Editor (press Alt+F11 or any other way you are used to get to the VBA Editor).
Make sure you are using the correct Excelbook by clicking once on the name. Now click on “insert” in the menubar and choose “Userform”. Click on the “combo box” control in the toolbox and click on the userform. Repeat this two times. Your Userform should now look like the userform in the picture below.
Setting the rowsource of the first combo box
The next important step is to set the rowsource of the first combobox. Select the first combo box and look at its properties on the left in the property explorer. Look for the option “Rowsource” and type the following =MOS_apps.
What you actually do here is referring to the named range you created earlier. Let us see if that worked as expected (see picture below).
Setting the rowsource of the second combobox
We don’t know what the row source of the second combo box is because we don’t know what option has been chosen from the first one. In other words the row source has to be set when the first choice has been made. Let’s say the user has chosen Excel as the first choice from the first combo box. The second combo box should in that case only show the options belonging to Excel. To achieve that you have to do the following:
Double-click on the first combo box. This will bring you to the change event for that combo box. Now enter the following code between the Sub and End Sub lines:
What we are doing here is setting the row source at runtime meaning that after the first choice has been made the second combo box will be filled. Now let us check to see if that actually worked:
It did. Now the last combo box needs to be filled. We do this the same way as we did the second box with the following steps:
Double-click on the second combo box and between the Sub and End Sub lines write the following code:
The third combo box will be filled with the appropriate list after the choice from the second combo box has been made. Let’s see if that worked:
You can easily create cascading combo boxes using lists from the worksheet. Name your lists and make sure you choose the change event of the combo box to set the row source. Now the last question is how to deal with double “naming” options in the worksheet. We will tackle this in the next blogpost about cascading combo boxes.
Happy Cascading 🙂
Download the sample file: