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

120+ Professional Project Management Templates!
Save Up to 85% LIMITED TIME OFFER

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.

Browse All Templates
Excel VBA Project Management Templates

All-in-One Pack
120+ Project Management
Premium Templates
View Details

Essential Pack
50+ Project Management
Premium Templates
View Details
50+ Excel
Project Management
Templates Pack
View Details
50+ PowerPoint
Project Management
Templates Pack
View Details
25+ MS Word
Project Management
Templates Pack
View Details
Ultimate Project Management Template
View Details
Ultimate Resource Management Template
View Details
Project Portfolio Management Templates
View Details
By Last Updated: June 17, 2022Categories: Excel VBATags:

Share This Story, Choose Your Platform!

36 Comments

  1. Jared February 20, 2014 at 6:24 AM

    I’d like to do the same thing as above with the first two comboboxes, but instead of using item hard-coded into the programming, use group of cells, so, where you see

    Select Case ComboBox1
    Case “Fruits”
    .AddItem “Apple”
    .AddItem “Pomegranate”
    .AddItem “Grape”
    .AddItem “Pineapple”
    .AddItem “Gouva”

    i’d like it to refer to cells that have those values in them, and tell it to keep looking untill it finds a blank cell (meaning theres no more values) so i can feel free to add and delete values as i want, right in the sheet. i’m sure this is possible, and i’ve seen it done, but can someone show me using the above example?

    thanks!

  2. PNRao February 20, 2014 at 11:18 PM

    Hi Jared,

    Here is the solution.
    Assuming that you have the following data in Column A and B:

    Fruits Apple
    Fruits Pomegranate
    Fruits Grape
    Fruits Pineapple
    Fruits Gouva
    Vegetables Tomato
    Vegetables Brinjal
    Vegetables Radish
    Vegetables Potato
    Vegetables Onion
    Beverages Pepsi
    Beverages Limca
    Beverages Miranda
    Beverages Sprite
    Beverages Coco Cola
    Soaps Lux
    Soaps Rexona
    Soaps Dove
    Soaps Lifeboy
    Soaps Liril

    Here is the code:


    Private Sub ComboBox1_Change()
    Dim iCntr As Integer

    iCntr = 1
    'Looping until column A is blank
    ComboBox2.Clear
    Do While Sheets("Sheet1").Cells(iCntr, 1) <> "
    'Checking if the column a is equals to ComboBox1 selected value
    If Sheets("Sheet1").Cells(iCntr, 1) = ComboBox1.Value Then
    'Adding items to ComboBox2, respective items in the Column B
    ComboBox2.AddItem Sheets("Sheet1").Cells(iCntr, 2)
    End If
    iCntr = iCntr + 1
    Loop

    End Sub

    Hope this helps.

    Thanks-PNRao!

  3. Amith April 25, 2014 at 1:12 PM

    Hi,
    I want to add quantity column and want a Total Quantity field in Combo Box with capability to auto sum quantity of Apple.(Suppose we have entered Apple in Row 2 as 100 Qty and Row 5 as 50 Qty in Data sheet.) And when anybody selects Fruit>Apple it will display Quantity as 150. This new part i want to add keeping rest of thing same. Need your help….

  4. PNRao April 26, 2014 at 10:52 AM

    Hi Amith,
    You can loop through the rows and check for the ‘apple’ and the add the respective qty. For example:
    in FruitComboBox_change()
    [vb]
    ‘Assuming Fruit Name in Column A and Qty in Column B
    If Trim(FruitComboBox.Value) <> "" Then
    lRow = 200 ‘Your last row in data sheet
    totQty = 0

    For iCntr = 1 To lRow
    If Sheets("Data").Cells(iCntr, 1) = FruitComboBox.Value Then totQty = totQty + Sheets("Data").Cells(iCntr, 2)
    Next
    ‘Display the final Qty
    QtyTextBox = totQty
    End If
    [/vb]

    Hope this helps!
    Thanks-PNRao!

  5. Bhawesh December 17, 2014 at 5:33 PM

    Dear sir,
    I would like to know that how i linked different cells rows with the same combobox which populated in different textboxes. kindly help.. Regards, Bhawesh

  6. sharmila January 5, 2015 at 10:05 PM

    good tutorial and well defined example

  7. PNRao January 6, 2015 at 8:09 PM

    Thank you Sharmila! We are glad we could help and hearing good feedback from our readers.
    -PNRao!

  8. Farbood February 13, 2015 at 11:10 PM

    Thank You! Very Perfect.

  9. Jayanth February 16, 2015 at 12:41 PM

    Hi!
    this is a good and easy to understand example.

    the query i have in my mind is that..

    i have a vb program and an excel sheet separately and i have linked it thru OLEDB connection.

    i have two combo boxes and i have to read data from excel sheet .

    how do i do that ?

  10. klorvalex February 25, 2015 at 10:07 PM

    Good. This helps me a lot. Thanks.

  11. PNRao March 2, 2015 at 7:17 PM

    Thank you klorvalex! You are most welcome to our blog. – PNRao!

  12. PNRao March 2, 2015 at 7:26 PM

    You can get the data from Excel to an arry and assign to ComboBox:
    Example:

    ComboBox1.List=ArrData

    Thanks-PNRao!

  13. PNRao March 2, 2015 at 7:29 PM

    You are most welcome Farbood! We are very glad and happy to hear such a nice feedback.
    Thanks-PNRao!

  14. xuan truong NGUYEN August 1, 2015 at 6:11 AM

    Your explanations and templates are excellent .Thanks a lot

  15. PNRao August 2, 2015 at 3:39 AM

    Thanks for the feedback!
    Regards-PNRao!

  16. japheth February 24, 2016 at 7:37 PM

    Hi guys, am stuck somewhere, av been able to create 2 combo boxes in my form and populated the first one. my question is how can i link the two combo boxes so that the list is combo box 2 depends with the user’s selection in combo box one, which fetches data from an excel worksheet.
    Any help?..Thanx in ADVANCE …

    THIS IS MY CODE (for populating combo box 1)

  17. PNRao February 25, 2016 at 10:35 PM

    Assuming your you have two combo boxes on user form named ComboBox1 and ComboBox2:

    'Initiating ComboBox1 
    Private Sub UserForm_Initialize()
    ComboBox1.AddItem "Set 1"
    ComboBox1.AddItem "Set 2"
    ComboBox1.AddItem "Set 3"
    End Sub
    
    'Populating ComboBox2 while changing the value of ComboBox1
    Private Sub ComboBox1_Change()
    ComboBox2.Clear
    If ComboBox1.Value = "Set 1" Then
        ComboBox2.List = Sheets("YourSheetName").Range("A1:A5").Value
    ElseIf ComboBox1.Value = "Set 2" Then
        ComboBox2.List = Sheets("YourSheetName").Range("B1:B5").Value
    ElseIf ComboBox1.Value = "Set 3" Then
        ComboBox2.List = Sheets("YourSheetName").Range("C1:C5").Value
    End If
    End Sub
    
    

    Hope this helps, Thanks-PNRao!

  18. Roy March 1, 2016 at 4:06 PM

    Hi PNRao,

    Hope you are doing good.
    a very small query. i have a code in which i am taking the input through input box and those values are getting stored in defined cells.what i want to do is the values that i am taking as an input if any of the values is less then a particular value say suppose 50 then that particular cell will return the value with a color red.
    i was doing the following but its not executing anything.

    Dim i As Integer
    i = 1
    Do While Cells(i, 2).Value ”
    If Cells(i, 2).Value < 50 Then
    Cells(i, 2).Font.Color = vbRed
    i = i + 1
    End If
    Loop

    help would be much appreciated.

  19. Marcel Defensor March 4, 2016 at 9:30 AM

    I just can’t create a combobox code that extracts its items direct from a worksheet dependind on the value related to a particular cell. Something like ‘if “a1=2” then “items are this column” else “items are that column”‘.
    Any help will be useful. Thanks.

  20. PNRao March 5, 2016 at 6:03 PM

    Hi Roy,
    increment statement should be out side the if block:

    Sub sbChangeFontColorBasedOnCellValue()
    Dim i As Integer
    i = 1
    Do While Cells(i, 2) <> "
        If Cells(i, 2) < 50 Then
            Cells(i, 2).Font.Color = vbRed
        End If
        i = i + 1
    Loop
    End Sub
    

    Thanks-PNRao!

  21. PNRao March 5, 2016 at 6:08 PM
  22. Jahanzeb May 5, 2016 at 4:13 PM

    Thanks a Lot, It will pay you for your kind support

  23. Bhupender May 16, 2016 at 1:46 AM

    Hi,

    Sir I am a new learner of vba. so could you please uploade the data file of above example. so that i can understand.

    Regards
    Bhupender Singh
    Big fan of yours tutorial

  24. Edgardo August 23, 2016 at 11:25 AM

    i want to select item’s quantity from combo-box and calculate price and it will input in textbox using Visual Basic Studio

  25. Edgardo August 23, 2016 at 11:26 AM

    Hi guys can you please help me? I want select item’s quantity from combo-box and calculate price.

  26. ravikumar August 31, 2016 at 3:48 PM

    hi,

    iam a new to vba code..i have an excel sheet in that I create dropdown list..like

    IF process is main then the list items are
    timeline
    kaizen
    datatabel
    fifolane

    when ever I will select the drop down list value according to that in next cell data will come autopopulate… can you send the code in vba

  27. ravikumar August 31, 2016 at 6:17 PM

    ‘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
    can you explain this code….
    here data in the sense wat…can you pls explain this…this will helpful for me…

  28. pradeep September 20, 2016 at 10:53 AM

    im unable to download the file

  29. PNRao October 23, 2016 at 9:46 AM

    Updated the link, please download the file.
    Thanks-PNRao!

  30. vasim January 10, 2017 at 2:22 PM

    please upload example excel file.

  31. Himanshu January 11, 2017 at 4:33 PM

    Dear Sir,

    Excellent tutorial. I need help with intermediate and advanced excel tutorials. Will you be able to provide tutorials in a structured course format. Thanks

  32. Rajesh May 29, 2017 at 1:55 AM

    Hi. I am stuck at a place where I am selecting the value from the first Combo box and according the value is listed in the second Combo box. Now the challenge for me here is the values of the second combo box is too long , during display the value is left aligned which is easier for the user to select the item but the once the value is selected the value is displayed right aligned which is difficult to identify what was selected. How can I show the value initially and on selecting also the value is left aligned.

    Kindly help.

  33. Catherine Thomas June 21, 2017 at 9:21 AM

    I downloaded this code and modified it for my 2 boxes and it works perfectly – thank you!!!

    Now, I have a project where I need to add a 3rd dependent box. Sticking with the .additem way, how Shoukd I wrote the code to get box 2 items to box?

    Thank you so much! I’m struggle with the combinations.

  34. Bhupesh August 21, 2017 at 6:28 PM

    Hello Sir,

    I want to know how to make a combo box on a userform with search suggestions.

    The combox shall have items in it already from which i can choose one. However if the no. of items are too many , then in that case i may write the item name and its suggestion shall be reflected.
    like if i type ” Ram” in the combobox then i shall get the suggestion like ” Ramesh” & ” Ramalingam” etc.

    Kindly help me in this .

  35. Chirag Prajapati January 24, 2018 at 3:46 PM

    Hi…
    Thanks for your tutorial.
    I get one problem.
    After coding as above i get result but after closing excel sheet program need to run manually.
    It doesn’t work with auto run.

  36. LLY February 15, 2019 at 5:44 PM

    Hiya,

    Would it be possible to use Loop to identify a group of combo box? For example :

    For i = 1 to 10
    Sheets(“Sheet1”).combobox(i).List = Array(“Apple”, “Banana”, “Coconut”)
    next i

    Thank you

Leave A Comment