How to Use ComboBox to Get Selected Item Utilizing VBA

Get FREE Advanced Excel Exercises with Solutions!

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.


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

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.

vba combobox selected item


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.

Using Elaborate Code

  • 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.

vba combobox selected item result

Read More: How to Select First Item from ComboBox Using VBA in Excel


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.

Applying Short Code

  • 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

vba combobox selected item with code

  • 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.

vba combobox selected item result


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.

Utilizing Selected Table from WorkSheet

  • 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

VBA combobox selected item with properties

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.

Read More: Insert ComboBox with Listindex in Excel VBA


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

Inserting Dynamic Range of Data from Worksheet

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.

vba combobox selected item result


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.

Download Practice Workbook

You can download the practice workbook from here.


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.


Related Articles

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.

Tags:

Mehedi Hasan Shimul
Mehedi Hasan Shimul

Md. Mehedi Hasan, with a BSc in Electrical & Electronic Engineering from Bangladesh University of Engineering and Technology, holds a crucial position as an Excel & VBA Content Developer at ExcelDemy. Driven by a deep passion for research and innovation, he actively immerses himself in Excel. In his role, Mehedi not only skillfully addresses complex challenges but also exhibits enthusiasm and expertise in gracefully navigating tough situations, emphasizing his steadfast commitment to consistently deliver exceptional and quality content.... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo