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

Premium Project Management Templates

120+ Professional Project Management Templates!

A Powerful & Multi-purpose Templates for project management. Now seamlessly manage your projects, tasks, meetings, presentations, teams, customers, stakeholders and time. This page describes all the amazing new features and options that come with our premium templates.

PREMIUM TEMPLATES
LIMITED TIME OFFER
ON SALE80% OFF
BROWSE ALL TEMPLATES

Advanced Project Planning Templates

Excel Templates

VIEW DETAILS

120+ Project Management Templates Pack

Excel | PowerPoint | Word

VIEW DETAILS

ULTIMATE RESOURCE MANAGEMENT TEMPLATE

Excel Template

VIEW DETAILS

50+ Essential Project Management Templates

Excel | PowerPoint | Word

VIEW DETAILS

Project Portfolio Management Templates

Excel | PowerPoint Templates

VIEW DETAILS

50+ Excel Project Management Templates

Excel Templates

VIEW DETAILS

By Published On: August 25th, 2013Categories: Excel VBATags:

Share This Story, Choose Your Platform!

About the Author: Valli

Excel VBA Developer having around 8 years of experience in using Excel and VBA for automating the daily tasks, reports generation and dashboards preparation. Valli is sharing to helps us automating daily tasks.