Showing Multiple Lists in a Single ListBox Dynamically

Multiple Lists in a Single ListBox
Multiple Lists in a Single ListBox

In this tutorial, we will learn how to make a listbox dynamic and switch to different data range on selection. We will also learn how to create a Dynamic Name with VBA code and assign it to ListBox RowSource.

You can use the logic and coding in your automation project. So, let’s start creating this from scratch.

Let us switch to Excel application. Create a new workbook and save with the name “Multiple List in a Single ListBox.XLSM”. Make sure to select .XLSM as extension.

Change the name of Sheet1 to Data. Create sample data and table for rowsource of Listbox. Follow the below image to create tables.

Tables for ListBox RowSource

So, here we have created three different tables. We will show all these Tables in ListBox on selection. It means when the user will select Month then ListBox will show the Month as list items and if the user will select Day then ListBox will show Day otherwise Listbox will show Student Table. 

Let us insert a command button to load and show the UserForm. To insert a command button, just click on Developer Tab, click on Insert then select Command Button under ActiveX Controls. Change the properties of command button as mentioned below.

Name: cmdShowForm Accelerator Key: S Caption: Show Form

Let us move to Visual Basic of Application Window. To switch to the VBA window, just click on Developer Tab then click on Visual Basic button available under code group.

Now, we are in the VBA window. Let us insert a UserForm. 

To insert a UserForm, just click on Insert Menu then Click on UserForm. Change the properties on UserForm as given below.

Form Name: frmForm; Form Caption: Dynamic List; Height:170; Width: 290

Insert a Frame and change the caption to “Show

Insert three Option buttons in frame 

Name:  optMonth, Caption: Month

Name:  optDay, Caption: Day

Name:  optStudent, Caption: Student 

Insert a Label and keep the name same as Label1 with caption “Selected Details

Insert a ListBox and give the name as lstDetails

Now, we have done with Form creation. Let’s start writing code.

Let us move to Form Code and write the code on form initialization to create dynamic names. We will use the names as a RowSource of Listbox.

Code for form initialization to create dynamic names

Write the code on the click event of Month option button.

Write the code on the click event of Day option button

Write the code on the click event of Day option button

Let’s write the code on click event of command button cmdShow

Code to show the UserForm

Now, we have completed all the coding and designing the form.

So, this is all about how to create a dynamic listbox and change the RowSource of ListBox on selection.

Watch our YouTube tutorial.

https://youtu.be/uNmcEZ539gs

Please click on below button to download the Excel file along with codes.

Download
Click to download

LEAVE A REPLY

Please enter your comment!
Please enter your name here