In this article I will explain the userform drop down list. This drop down list is actually called a combo box. It is used in user forms.

Jump To:

• Creating
• Adding Items
• Inserting Items
• Modifying Items
• Removing Items
• Get Selected Index
• Get Selected Item
• Item Count

You can download a complete example from the link below:

• Drop Down Lists, User Form.xlsm

Creating:

In order to create one of these drop down lists (combo boxes) all you need to do is click on the combo box on the tool box and click somewhere on the userform where you want to insert it: As  you can see in the figure above the user has created a drop down list for the user form.

Adding Items:

In order to add an item to a userform drop down list (combo box)  you could use the function AddItem(). The code below adds the string ”strValue”  to the the combo box.

ComboBox1.AddItem ("strValue")

Result:

Inserting Items:

Lets say there is a drop down  list (combo box) with 6 items: Using the function below you can insert a value in the drop down list:

ComboBox1.AddItem(Value, Index)

Value: The value to insert. Index: The index to insert the value. In the figure below you can see where the value will be inserted based on the index value: You can see some example below:

Call ComboBox1.AddItem("New Value", 0)

In the example below the new value will be inserted at the index 1:

Call ComboBox1.AddItem("New Value", 1)

In the example below the new value will be inserted at the end of the list of values:

Call ComboBox1.AddItem("New Value", 6)

Modifying Items:

Drop down list (combo box) values start from the index “0”. If you know the index of an item you can use the code below to modify it:

ComboBox1.List(Index) = NewValue

Index: The index of the item to modify. NewValue: The value to modify the item to. For example lets say we have the following data in the drop down list: The following code will change the first item to “New Value”:

ComboBox1.List(0) = "New Value"

Result:

Removing Items:

If you want to remove all the items from a drop down list (combo box) you can use the code below:

ComboBox1.Clear

If you know the index of a specific item you can use the code below to remove it: Note: Drop down lists are zero based, therefore the first item has the index “0” and the last item has the index “n-1” where “n” is the number of items in the drop down list.

ComboBox1.RemoveItem (Index)

In the example below we have the following items in the drop down list: Using the code below the first item will be removed:

ComboBox1.RemoveItem (0)

Result:

Get Selected Index:

Using the code below you can get the currently selected index in the drop down list:

Dim intSelectedIndex As Integer intSelectedIndex = ComboBox1.ListIndex

In the example below whenever the user selects a new value from the drop down list a message box will appear displaying the currently selected index:

Private Sub ComboBox1_Change() MsgBox (ComboBox1.ListIndex) End Sub

The code above uses a ComboBox1_Change() event handler. The event handler executes when the user selects a new value from the drop down list. The user selects “Value 4” from the drop down list: Result: Note: Drop down lists are zero based, therefore the first item has the index “0” and the last item has the index “n-1” where “n” is the number of items in the drop down list.

Get Selected Item:

In the previous section I’ve explained how to get the selected index. Using the selected index you can get the selected item using the code below:

Private Sub ComboBox1_Change() If ComboBox1.ListIndex <> -1 Then     MsgBox (ComboBox1.List(ComboBox1.ListIndex)) End If End Sub

The code above uses a ComboBox1_Change() event handler. The event handler executes when the user selects a new value from the drop down list.  The IF statement checks if something is selected. If something is selected the selected value is displayed in a message box. In the figure below the user selects “Value 5”: Result:

Item Count:

The code below returns the number of items in the drop down list:

Dim intCount As Integer intCount = ComboBox1.ListCount

Assume we have the following date in the drop down list: The code below will display the item count in a drop down list:

Dim intCount As Integer intCount = ComboBox1.ListCount MsgBox (intCount)

Result:

You can download a complete example from the link below:

• Drop Down Lists, User Form.xlsm

See also:

• Excel VBA Drop Down Lists Using Data Validation
• Excel Drop Down Lists

If you need assistance with your code, or you are looking for a VBA programmer to hire feel free to contact me. Also please visit my website  www.software-solutions-online.com