Excel Macro Tutorial : ComboBox in Excel Macro

.

It’s been a long time since I posted an article :(. Today I am going to write about Combo Box in Excel. At the end of this Article, you will be able to use Drop down in Excel. It will include
 
1. Adding Items in Excel Combobox dor Excel Drop Down
2. Removing Items from Drop down
3. Removing All items from Drop Down
4. Selecting options from Drop down by position in the drop down
5. Selecting options from Drop down by Value of the option in the drop down
6. Excel Drop Down Default Value
7. How to get the Total Count of Items in a Combobox
 
…..and many more
things about Excel Combobox.

First will learn how to place Drop Down or Combobox in your Excel Sheet.
By going to Developer Tab –> Insert –> Combo Box Activex Control

Excel Macro Tutorial - Add Combo Box 1

Excel Macro Tutorial – Add Combo Box 1


Now Drag and Drop that Control any where you want in your Spreadsheet.
You can Also re-size the Height and Width etc keeping it in Design Mode

Excel Macro Tutorial - Combo Box in Design Mode

Excel Macro Tutorial – Combo Box in Design Mode

Now as you can see that Drop Down is created in the Sheet, you will learn How to Add Options or Items in the Drop down. As of Now it is completely blank. There is no option/item in it.
Basically there are two ways of Adding list Items in Drop Down List. 1. By Setting Range Formula in the Properties of Combo Box.
 
2. By using Excel VBA Code

Add List Items in Drop Down by Using Properties:

To Add items by setting Properties of the Combo Box, follow the below Steps:
 
Step 1. Select the Combo Box Control and Right Click and Open the Properties Window
Step 2. Now Enter the Cell Range in ListFillRange Property as shown below:

Excel Macro Tutorial - Combo Box - ListFillRange Property

Excel Macro Tutorial – Combo Box – ListFillRange Property


Now whatever List Items you want to be there in the Drop Down, Type them in that Range in Excel Sheet. All the list available in that Range will start appearing in the Drop Down as shown below:

Excel Macro Tutorial - Drop Down List in Excel

Excel Macro Tutorial – Drop Down List in Excel

Add List Items in Drop Down by Using Excel Macro (VBA Code):

In Drop Down List Box, all the Items are by default Indexed from 0, 1, 2 etc. The first item which is added to Drop Down List will be indexed as 0, Second One will be Indexed as 1 and so on.. Therefore no need of any indexing here while adding list items in the Drop Down List.
To Add Item List in Drop Down List, there is a very Simple VBA Syntax as shown Below:
 
<ComboBoxName>.AddItem <List Item> , <Index Number >
 


Sub Insert_Item_List()

Sheet1.ComboBox1.AddItem "Option 1", 0
Sheet1.ComboBox1.AddItem "Option 2", 1
Sheet1.ComboBox1.AddItem "Option 3", 2
Sheet1.ComboBox1.AddItem "Option 4", 3
Sheet1.ComboBox1.AddItem "Option 5", 4

End Sub

In the Above Code, Sheet1 is the Sheet Name where Combobox is there and ComboBox1 is the Name of the ComboBox Control.

Important:
In Combobox, when you are adding a New Item, It always gets added at the End of the List Which are already assigned to that Drop Down List. It means, It always appends the items to the list. So Every time you want a fresh List of Items in your Drop down, then before adding any new Item, Clear all the Existing Items from the Drop Down. Below is the Code How to Clear All the Items from the Drop Down List
 

 
Sheet1.ComboBox1.Clear
 

Most of the time you add List Items dynamically. For Example: you have some list of Values stored in an Array Variable and you want all of them to Add in the Drop Down List. In the below Code, you will learn how to Add List Items from an Array Variable using For loop.



Sub Insert_Item_List()

Dim iCounter As Integer
Dim Item(10) As String
'Store 10 List Items in an Array Variable
For i = 0 To 9
  Item(i) = "Option " & i + 1
Next
'Now Add all these items from Array Variable to Combo Box
For i = 0 To 9
  Sheet1.ComboBox1.AddItem Item(i)
Next
End Sub

Take the Above Code and Paste it in a Regular Module and Run it after adding a Combobox in your Sheet. It will Add 10 Items in the Drop down from “Option 1” to “Option 10”. But there is a problem in Above code. If you run the same Code twice, in your Drop down there will be 20 list get added, if you run thrice then 30 and so on. Why? because as i told earlier that .AddItem always append the list. It does not clear the Previous ones and then add the new one. To overcome this Problem, we need to put a Statement to Clear all the Items before you add list in the Drop Down list box. Therefore in the below code, this problem will not occur.



Sub Insert_Item_List()

Dim iCounter As Integer
Dim Item(10) As String
'Store 10 List Items in an Array Variable
For i = 0 To 9
  Item(i) = "Option " & i + 1
Next
'Before Adding items to the List, Clear the Drop Down Box
  Sheet1.ComboBox1.Clear
'Now Add all these items from Array Variable to Combo Box
For i = 0 To 9
  Sheet1.ComboBox1.AddItem Item(i)
Next
End Sub

As you saw how to Clear or Remove All the Items from the Drop down. Now you will learn how to remove a particular Item from Drop Down List

If you want to Remove a particular Item from the Drop Down then use the below code to remove item from the Drop Down List. For Removing an Item, you need to pass Index Number as an Input Parameter.

 
<ComboBoxName>.RemoveItem <Index Number >
 


Sheet1.ComboBox1.RemoveItem 0

The Above Code will Always Remove the First List Item from the Drop Down.

How to Select First List Item as by Default:

As you can see by default no List value is selected after adding items. If you want to make Some List Item as Default One then follow the below.
 
<ComboBoxName>.ListIndex= <Index Number >
 
To select an Item from the Drop Down you need to use the below Code
 



Sheet1.ComboBox1.ListIndex = 0

 
In the Above example, First Item will be by default selected as I have used the Index Number as Zero (0). Similarly if you want to Select second or Third… give the Index Number of that Item.

Want to Select Blank Option in Drop Down Box: To Select Blank Option from the Drop Down List, you should use Index Number as -1.
 



Sheet1.ComboBox1.ListIndex = -1

If you want to Select the Last Item of the Drop Down List By default, then use the below Code:
 



Sheet1.ComboBox1.ListIndex = Sheet1.ComboBox1.ListCount - 1

How to Get Selected Value of the Drop Down List:

Below is the Simple Code which will show you how to get the Selected Value of the Drop Down. .Value property of the Control ComboBox1 returns the Selected Value of the Drop Down.

 



MsgBox "Selected Value is" & ComboBox1.Value

How to Get Total Count of Items in a ComboBox

.ListCount is a Property in Of ComboBox object to get the Total Number of Items in a ComboBox. .ListCount always returns a Number.
Refer the Below Code. It will give the the Total Number of Items in the ListBox1


MsgBox (ComboBox1.ListCount)

 
To Check out more Excel Macro Tutorials, visit Excel Macro Tutorial

Buy a coffee for the author

Adsense

Download FREE Tools and Templates

There are many cool and useful excel tools and templates available to download for free. For most of the tools, you get the entire VBA code base too which you can look into it, play around it, and customize according to your need.

Dynamic Arrays and Spill Functions in Excel: A Beginner’s Guide
Dynamic Arrays and Spill Functions in Excel: A Beginner’s Guide

In today's tutorial, we'll be diving into the exciting world of dynamic arrays and spill functions in Office 365 Excel. These features have revolutionized the way we work with data, providing a more flexible and efficient way to handle arrays. I am going to explain...

How to Declare a Public Variable in VBA
How to Declare a Public Variable in VBA

While programming in VBA sometimes you need to declare a Public Variable that can store the value throughout the program. Use of Public Variable: Let's say you have 4 different Functions in your VBA Code or Module and you have a variable that may or may not be...

How to Copy content from Word using VBA

As many of us want to deal with Microsoft Word Document from Excel Macro/VBA. I am going to write few articles about Word from Excel Macro. This is the first article which opens a Word Document and read the whole content of that Word Document and put it in the Active...

What is Excel Formula?

Excel Formula is one of the best feature in Microsoft Excel, which makes Excel a very very rich application. There are so many useful built-in formulas available in Excel, which makes our work easier in Excel. For all the automated work, Excel Macro is not required. There are so many automated things can be done by using simple formulas in Excel. Formulas are simple text (With a Syntax) which is entered in to the Excel Worksheet Cells. So how computer will recognize whether it is a formula or simple text? Answer is simple.. every formula in Excel starts with Equal Sign (=).

You May Also Like…

13 Comments

  1. Marcelo Liascovich

    Thank you Dear friends it me make me HAPPY to receive This material. The best for you

    Reply
    • Vishwamitra Mishra

      You are Welcome Marcelo. If you need any other material you can email me. It will be posted very soon here 🙂

      Info@learnExcelMacro.com

      Reply
      • sathish

        hi,

        Vishwamitra i need the Macro notes for beginner learning , with Few Exaples for practice..

        Reply
        • Vishwamitra Mishra

          Hi Sathish,

          You can enter your email ID and subscribe to the website. Then you will receive a link to download an E-Book for beginners.

          You can also go through the Excel Macro Tutorials for beginners: http://www.learnexcelmacro.com/excel-macro-tutori

          Reply
      • Sathish

        Hi,

        We are trying extract all the test cases from Quality Center including Regression priority.Is there any code available for this scenario

        Reply
        • Sathish

          From a particular folder and extracting all test cases to one excel sheet we need to separate the test cases by using Regression Priority(which means we have to separate test cases if we give extract priority as P1 or P2 or P3 or P1 & P2& P3)

          Expecting valuable answers soon.
          Would feel great if anyone gives answer for that

          Reply
  2. Siddharth Jain

    Great stuff there in website. The best thing is that every tutorial contains so much of detail that even a novice can understand this. Brilliant!

    Reply
    • Vishwamitra Mishra

      Thanks Siddharth !!

      Reply
  3. sriam

    Super Thanks for Wonderful Tutorial

    Reply
  4. Anwaar

    Great JOb.!! Really

    Reply
  5. unnikrishnan a

    excellent notes ,any body can follow your notes.thanks to the good work. God bless you

    Reply
    • Vishwamitra Mishra

      Thanks Unnikrishnan !!

      Reply

Submit a Comment

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Join and get a FREE! e-Book

Don't miss any articles, tools, tips and tricks, I publish here

You have Successfully Subscribed!

Pin It on Pinterest