VBA List Box

The list box in Excel VBA is a list assigned to a variable. This list has various inputs to select from and allows selecting multiple options at once. A list box can be inserted on a UserForm by choosing the list box option.

List boxes use named ranges having certain values. The purpose of using a list box is to display a list of values that users can choose from.

The steps to create a list box in VBA are listed as follows:

• Step 1: Right-click on the list box and make sure the Design Mode is selected. This links the list box to a cell.
• Step 2: In “properties,” go to the “LinkedCell” property and fill in “B17” as shown in the following image.

• Step 3: In “ListFillRange,” enter “Month.”

• Step 4: The list box appears as shown in the succeeding image.

The data in cell B17 changes when you change your choice in the list box. This is shown in the following image.

The steps to create a list box on a UserForm are listed as follows:

• Step 1: In the Developer tab, click “Visual Basic” from the Code or Press Alt+F11.

• Step 2: In the Insert menu, click “UserForm.”

• Step 3: From the toolbox, drag the list box control on the “UserForm.”

• Step 4: Double-click on “UserForm” and select “UserForm event,” as shown in the following screenshot.

• Step 5: Add the following code to the in-between procedure.

Private Sub UserForm_Initialize()

ListBox1.AddItem "MBA"

ListBox1.AddItem "MCA"

ListBox1.AddItem "MSC"

ListBox1.AddItem "MECS"

ListBox1.AddItem "CA"

End Sub

• Step 6: Press F5 to run the code.

The steps to add a dynamic control to the list box on the UserForm using VBA are listed as follows:

• Step 1: From the toolbox, add a “CommandButton” on the “UserForm.”

• Step 2: Right-click on the “Commandbutton” and click “properties.”

• Step 3: Change the caption of the “CommandButton” to “Create_Listbox.”

• Step 4: Double-click on the command button and the following code appears.

Private Sub CommandButton1_Click()

End Sub

• Step 5: Name the following procedure as “Add_Dynamic_Listbox.”

Sub Add_Dynamic_Listbox()

'Add Dynamic List Box and assign it to object 'LstBx'

Set LstBx = UserForm3.Controls.Add("Forms.ListBox.1")

'List Box Position

LstBx.Left = 20

LstBx.Top = 10

End Sub

• Step 6: Click F5 to run the macro.

The code to add items to list box control in VBA is mentioned as follows:

Private Sub Insert _Items _To_LstBox ()

ListBox1.AddItem "Item 1"

ListBox1.AddItem "Item 2"

ListBox1.AddItem "Item 3"

ListBox1.AddItem "Item 4"

ListBox1.AddItem "Item 5"

End Sub

The code to clear items from the list box control in VBA is mentioned as follows:

Sub Clr_LstBx()

UserForm3.ListBox1.Clear

End Sub

Let us say we have daily sales data. We want to prepare a graphical user interface of this sales data that allows us to select items from a list box. To fetch the sales details, we have to change the Index cell to “cell link” and apply the VLOOKUP formula as shown in the following images.

The constants, their values, and description are given in the following table: