VBA ComboBox Excel Macros Examples Codes Adding Clearing Items

Home/Excel VBA/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.

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

Business Presentations Templates Pack
PowerPoint Slides

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
LIMITED TIME OFFER - Get it Now!
Business PowerPoint Presentations Templates Pack
 
 
Related Resource External VBA Reference
By |August 25th, 2013|Excel VBA|34 Comments

About the Author:

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.

34 Comments

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

    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!

    • PNRao February 20, 2014 at 11:18 PM - Reply

      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!

      • Amith April 25, 2014 at 1:12 PM - Reply

        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….

        • PNRao April 26, 2014 at 10:52 AM - Reply

          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!

      • vasim January 10, 2017 at 2:22 PM - Reply

        please upload example excel file.

  2. Bhawesh December 17, 2014 at 5:33 PM - Reply

    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

  3. sharmila January 5, 2015 at 10:05 PM - Reply

    good tutorial and well defined example

    • PNRao January 6, 2015 at 8:09 PM - Reply

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

  4. Farbood February 13, 2015 at 11:10 PM - Reply

    Thank You! Very Perfect.

    • PNRao March 2, 2015 at 7:29 PM - Reply

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

  5. Jayanth February 16, 2015 at 12:41 PM - Reply

    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 ?

    • PNRao March 2, 2015 at 7:26 PM - Reply

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

      ComboBox1.List=ArrData

      Thanks-PNRao!

  6. klorvalex February 25, 2015 at 10:07 PM - Reply

    Good. This helps me a lot. Thanks.

    • PNRao March 2, 2015 at 7:17 PM - Reply

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

  7. xuan truong NGUYEN August 1, 2015 at 6:11 AM - Reply

    Your explanations and templates are excellent .Thanks a lot

    • PNRao August 2, 2015 at 3:39 AM - Reply

      Thanks for the feedback!
      Regards-PNRao!

  8. japheth February 24, 2016 at 7:37 PM - Reply

    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)

    • PNRao February 25, 2016 at 10:35 PM - Reply

      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!

  9. Roy March 1, 2016 at 4:06 PM - Reply

    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.

    • PNRao March 5, 2016 at 6:03 PM - Reply

      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!

  10. Marcel Defensor March 4, 2016 at 9:30 AM - Reply

    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.

  11. Jahanzeb May 5, 2016 at 4:13 PM - Reply

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

  12. Bhupender May 16, 2016 at 1:46 AM - Reply

    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

  13. Edgardo August 23, 2016 at 11:25 AM - Reply

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

  14. Edgardo August 23, 2016 at 11:26 AM - Reply

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

  15. ravikumar August 31, 2016 at 3:48 PM - Reply

    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

  16. ravikumar August 31, 2016 at 6:17 PM - Reply

    ‘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…

  17. pradeep September 20, 2016 at 10:53 AM - Reply

    im unable to download the file

    • PNRao October 23, 2016 at 9:46 AM - Reply

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

  18. Himanshu January 11, 2017 at 4:33 PM - Reply

    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

  19. Rajesh May 29, 2017 at 1:55 AM - Reply

    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.

  20. Catherine Thomas June 21, 2017 at 9:21 AM - Reply

    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.

  21. Bhupesh August 21, 2017 at 6:28 PM - Reply

    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 .

Leave A Comment