VBA ComboBox Excel Macros Examples Codes Adding Clearing Items

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:

• Add Items to ComboBox while opening Workbook
• Add Items to ComboBox2 based on ComboBox1 selection
• Get data to TextBox based on ComboBox2 selection
• Clear ComboBox Items
• DownLoad:Example File

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:

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.

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.

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.

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