How to Use ComboBox to Get Selected Item Utilizing VBA

In this article, we will demonstrate the use of the ComboBox to get a selected item using VBA code. We can specify the items for the ComboBox directly in the code or we can use data from worksheets. For demonstration purposes, we’ll use the 7 weekdays in the dataset below.

Step 1 – Using Elaborate VBA Code to Get a Selected Item

In this example, the code to use a ComboBox to select an item is quite elaborate.

Open the VBA window by pressing Alt + F11. Or, go to the Developer tab > Visual Basics.
From the VBA Projects, right-click on the active sheet and select Insert > UserForm.

Right-click on the userform and select Properties.

Give a name to the userform in the Caption section.

Select ComboBox from the Toolbox. If the Toolbox is not present automatically, select View > Toolbox from the VBA window.

Left-click and drag the mouse to create the ComboBox in the userform.

Double-click on the ComboBox and a code window will appear.
In the top section of the window, select UserForm and Initialize.
Enter the following code in the code window:

Private Sub UserForm_Initialize()
'for combobox1
Me.ComboBox1.AddItem "Saturday"
Me.ComboBox1.AddItem "Sunday"
Me.ComboBox1.AddItem "Monday"
End Sub

Click on the Run button in the VBA window.

The ComboBox is inserted in our worksheet. We can select Saturday, Sunday, or Monday from the ComboBox as we included them in the code.

Step 2 – Using a Short VBA Code in the ComboBox to Get a Selected Item

Now we’ll go through almost same procedure to make the ComboBox select items, but with a short version of the code.

Create a second ComboBox in the same manner as we created the first ComboBox.

Double-click on it and enter the following additional code in the code window for the second ComboBox.

Private Sub UserForm_Initialize()
'for combobox1
Me.ComboBox1.AddItem "Saturday"
Me.ComboBox1.AddItem "Sunday"
Me.ComboBox1.AddItem "Monday"
'for combobox2
With Me.ComboBox2
.AddItem "Tuesday"
.AddItem "Wednesday"
End With
End Sub

Run the code.

The second ComboBox from where we can select Tuesday and Wednesday is inserted.

Step 3 – Using a Selected Table from the Worksheet

We can avoid writing code to create the ComboBox to get the selected item. Instead, we can include the ComboBox items from a table in the worksheet.

Select the table from the worksheet and give it a name in the upper left side of the worksheet, such as Weekdays.

Go to the VBA window and create another ComboBox in the userform.
Go to the Properties for ComboBox3 and enter the following code in the RowSource section:

=Weekdays

Note: If you used a different table name, use that here instead of WeekDays.

Click Run in the VBA window.

A third ComboBox is inserted in the worksheet from where we can select the elements of the table.

Step 4 – Inserting a Dynamic Range of Data from the Worksheet

Now we’ll include updated data from the worksheet in the ComboBox.

Create a fourth ComboBox and double-click on it.
In the code window, enter the following additional code:

Private Sub UserForm_Initialize()
'for combobox1
Me.ComboBox1.AddItem "Saturday"
Me.ComboBox1.AddItem "Sunday"
Me.ComboBox1.AddItem "Monday"
'for combobox2
With Me.ComboBox2
.AddItem "Tuesday"
.AddItem "Wednesday"
End With
'for combobox3 no code required
'for combobox4
Dim cnt As Integer
cnt = Application.WorksheetFunction.CountA(Range("B:B"))
For i = 4 To cnt
    Me.ComboBox4.AddItem Cells(i, 2)
Next i
End Sub

Note: in the code part: For i = 4 To cnt, we selected i=4 as the data in the worksheet starts at row 4. And in part: ComboBox4.AddItem Cells(i, 2), we included 2 as our data is in column 2. Change these values according to your dataset.

Run the code.

The fourth ComboBox is inserted in the worksheet, from where we can select all the elements from the table. Any additional data in entered into the table will be automatically included in the ComboBox.

Things to Remember

After writing each part of the code, don’t forget to save the workbook as file-type xlsm.
Opening multiple xlsm workbooks at the same time may create problems. Rather close other xlsm workbooks while working on this workbook.

Download Practice Workbook

Combobox to Get Selected Item.xlsm