VBA ComboBox Excel Macros Examples Codes for Adding new Items,Adding new Items to another ComboBox based on selection of first ComboBox ,Clearing Tutorials. ComboBox in Excel VBA is one of most useful control in the Excel. You can show the list of items in the ComboBox and user can select any one item and do different operations. In this tutorial, we will explain different example on using ComboBox.

ComboBox in Excel VBA – Example Cases:

Sample ComboBox Design:

  1. GoTo Developer Tab from Menu
  2. GoTo Insert from Controls Part
  3. Insert two ComboBox’s and two TextBox’es from ActiveX Controls
  4. The final design should be as shown below
Screen Shot:

ComboBox in Excel VBA-Design1

Add Items to ComboBox while opening Workbook

You can Add items to the ComboBox while opening the Excel Workbook. The following example will show you how to populate the items in ComboBox while opening excel file.

Code:
Private Sub Workbook_Open()
Application.EnableEvents = False
'Clear ComboBox1 Items
Call Clear_ComboBox
'Add Items to ComboBox1 in Sheet1 while opening workbook
With Sheet1.ComboBox1
.AddItem "Fruits"
.AddItem "Vegetables"
.AddItem "Soaps"
.AddItem "Beverages"
End With
Application.EnableEvents = True
End Sub
Output:

Here is the screen-shot of the ComboBox1 with items.
ComboBox in Excel VBA-Add Items

Instructions:
  1. Open an excel workbook
  2. Press Alt+F11 to open VBA Editor
  3. From Project Explorer Double Click on ThisWorkbook
  4. Copy the above code and Paste in the code window
  5. Save the file as macro enabled workbook and Close it
  6. Open the file to see the output
  7. You should see the Items in ComboBox1 as shown above
Add Items to ComboBox2 based on ComboBox1 selection

You can add items to ComboBox2 based on ComboBox1 selection. It is helpful while developing tools. You can provide the user to select item from first ComboBox and add items to second comboBox based on first ComboBox Selection.Please find the following example below.

Code:
'Get Items to ComboBox2 based on ComboBox1 selection
Private Sub ComboBox1_Change()
'Variable Declaration
Dim iCnt As Integer
'Clear Combobox2 before loading items
ComboBox2.Clear
With ComboBox2
Select Case ComboBox1
Case "Fruits"
.AddItem "Apple"
.AddItem "Pomegranate"
.AddItem "Grape"
.AddItem "Pineapple"
.AddItem "Gouva"
Case "Vegetables"
.AddItem "Tomato"
.AddItem "Brinjal"
.AddItem "Radish"
.AddItem "Potato"
.AddItem "Onion"
Case "Beverages"
.AddItem "Pepsi"
.AddItem "Limca"
.AddItem "Miranda"
.AddItem "Sprite"
.AddItem "Coco Cola"
Case "Soaps"
.AddItem "Lux"
.AddItem "Rexona"
.AddItem "Dove"
.AddItem "Lifeboy"
.AddItem "Liril"
End Select
End With
End Sub
Output:

Here is the screen-shot to show you adding the items to second ComboBox based on first ComboBox Selection.
ComboBox in Excel VBA-Add Items to ComboBox2

Instructions:
  1. Please follow the above mentioned design steps
  2. Goto Developer Tab from the menu, Click on Design Mode in the Sheet1
  3. Double Click on ComboBox1
  4. Copy the above code and Paste in the code window
  5. Goto Developer Tab from the menu, Click on Design Mode in the Sheet1
  6. Now select Item from Combox1. Now you can see the items in Combox2 based on ComboBox1 selection
Get data to TextBox based on ComboBox2 selection

The following example will show you how to get data to TextBox based on ComoBox2 selection.

Code:
'Get Price based on ComboBox2 selection
Private Sub ComboBox2_Change()
'Variable Declaration
Dim iRow As Integer
iRow = 1
'Clear Combobox2 before loading items
TextBox1.Text = ""
'Get Price based on ComboBox2 selection
Do
iRow = iRow + 1
Loop Until ComboBox2.Text = Sheets("Data").Cells(iRow, 3)
TextBox1.Text = Sheets("Data").Cells(iRow, 4)
TextBox2.Text = Sheets("Data").Cells(iRow, 5)
End Sub
Output:

Here is the sample screen-shot.
ComboBox in Excel VBA-Output

Instructions:
  1. Please follow the above mentioned design steps
  2. Goto Developer Tab from the menu, Click on Design Mode in the Sheet1
  3. Double Click on ComboBox2
  4. Copy the above code and Paste in the code window
  5. Goto Developer Tab from the menu, Click on Design Mode in the Sheet1
  6. Now select Item from Combox1 & Combox2. Now you can see the Price and availability of item to the Textbox as aoutput based on ComboBox2 selection
Clear ComboBox Items

You can clear the ComboBox using Clear method. The following procedure will show how to clear the ComboBox items, this procedure will clear the ComboBox items before loading an items to ComboBox.

Code:
Sub Clear_ComboBox()
'Clear ComboBox & TextBox data
With Sheet1
.ComboBox1.Clear
.ComboBox2.Clear
.TextBox1.Text = ""
.TextBox2.Text = ""
End With
End Sub
Instructions:
  1. Open an excel workbook
  2. Press Alt+F11 to open VBA Editor
  3. Insert a new module from Insert menu
  4. Copy the above code and Paste in the code window
  5. We can call this procedure to clear ComboBox items before loading items to ComboBox
  6. It will clear items from ComboBox
Example File

Download the example file and Explore it.
ANALYSISTABS-Combo Box

Hey! Join Our Community

Get Quick Responses & Experts' Answers in Minutes!
Get Notified - When Answered Your Question!