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

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

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:

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:

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