How to Use ComboBox to Get Selected Item Utilizing VBA

ComboBox is a useful tool in Excel. We can use the ComboBox to select specific item utilizing a bit of VBA code. We can specify the items for the ComboBox directly into the code or we can use data from worksheets as the items for ComboBox. Here, I will show the use of ComboBox to get selected item utilizing VBA code.

Table of Contents hide

Download Practice Workbook

Step-by-Step Procedures to Use ComboBox to Get Selected Item Utilizing VBA

STEP 1: Use Elaborate VBA Code to Get Selected Item

STEP 2: Apply Short VBA Code for ComboBox to Get Selected Item

STEP 3: Utilize Selected Table from Worksheet

STEP 4: Insert Dynamic Range of Data from Worksheet

Things to Remember

Conclusion

Download Practice Workbook

You can download the practice workbook from here.

Combobox to Get Selected Item.xlsm

Step-by-Step Procedures to Use ComboBox to Get Selected Item Utilizing VBA

In this section, I will show step-by-step procedures to use ComboBox to get selected item utilizing VBA code. For demonstration, I have included 7 weekdays in the dataset.

STEP 1: Use Elaborate VBA Code to Get Selected Item

We can make the ComboBox select an item using a bit of elaborate code. For that, follow the procedures given below.

First, open the VBA window by pressing Alt + F11. Also, you can select the Developer tab > Visual Basics.
Then, from the VBA Projects right-click on the active sheet and select Insert > UserForm.

Afterward, right-click on the userform and select Properties.

After that, give a name to the userform in the Caption section.

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

Further, left-click and drag the mouse for creating the ComboBox in the userform.

Later on, double-click on the ComboBox and a code window will appear.
Furthermore, in the top section of the window select UserForm and Initialize.
Next, write 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

Then, click on the Run button in the VBA window.

Finally, we will see the ComboBox in our worksheet. We can select Saturday, Sunday, Monday from the ComboBox as we included them in the code.

STEP 2: Apply Short VBA Code for ComboBox to Get Selected Item

In this section, I will walk you through almost same procedures to make ComboBox select items but with a short version of code.

First, create a second ComboBox as we created the first ComboBox.

Next, double-click on it and write a little 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

Finally, Run the code and we will see the second ComboBox from where we can select Tuesday and Wednesday as we included them in the code.

STEP 3: Utilize Selected Table from Worksheet

We can avoid writing code for creating the ComboBox to get the selected item. We will include the ComboBox items from a table in the worksheet. Follow the procedures given below for that.

First, select the table from the worksheet and give it a name on the upper left side of the worksheet. I have given the name Weekdays.

Then, go to VBA window and create another ComboBox in the userform.
Next, go to properties for ComboBox3 and write the following code in the RowSource section.

=Weekdays

Note: You need to insert the name you have used for the table instead of WeekDays.

Finally, press Run in the VBA We will see a third ComboBox in the worksheet from where we can select the element of the table.

STEP 4: Insert Dynamic Range of Data from Worksheet

In this section, I will show the procedure to include data from worksheet to the ComboBox. This time the updated data from the worksheet will be included in the ComboBox. Follow the procedures given below.

First, create the fourth ComboBox and double-click on it
Then, in the code window write an additional code. The whole code with additional pieces is given below.

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 I have selected i=4 as my data in the worksheet starts at row 4. And in part: ComboBox4.AddItem Cells(i, 2) I have included 2 as my data is in column 2. You can change them according to your dataset.

Finally, Run the code and we will see the fourth ComboBox in the worksheet from where we can select all the elements from the table. If we add more data in the table, they will be included in the ComboBox.

Things to Remember

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

Conclusion

ComboBox is a quite useful tool for selecting items in Excel. Here, I have shown step-by-step procedures to create different ComboBox to get selected items utilizing VBA. If you face any problems following the procedures, please leave comments. Visit our ExcelDemy Website for similar articles regarding Excel.