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.

PREMIUM TEMPLATES LIMITED TIME OFFER

ON SALE80% OFF

BROWSE ALL TEMPLATES

50+ Project Management Templates Pack
Excel PowerPoint Word

VIEW DETAILS

Advanced Project Plan & Portfolio Template
Excel Template

VIEW DETAILS

Ultimate Project Management Template
Excel Template

VIEW DETAILS

20+ Excel Project Management Pack
Excel Templates

VIEW DETAILS

20+ PowerPoint Project Management Pack
PowerPoint Templates

VIEW DETAILS

10+ MS Word Project Management Pack
Word Templates

VIEW DETAILS


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

LIMITED TIME OFFER - Get it Now!
Advanced Project Plan Excel Template

 
Related Resource External VBA Reference