Tags

, , , ,

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:

You can download a complete example from the link below:


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: Adding a drop down list to the user form 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: VBA, Add, Value Drop Down List, strValue


Inserting Items:

Lets say there is a drop down  list (combo box) with 6 items: VBA, Drop Down List, 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: VBA, Drop Down List, Insert, Index You can see some example below:

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

VBA, Drop Down List, Insert, Index 0 In the example below the new value will be inserted at the index 1:

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

VBA, Drop Down List, Insert, Index 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)

VBA, Drop Down List, Insert, Index End


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: VBA, Drop Down List, 6 Items The following code will change the first item to “New Value”:

ComboBox1.List(0) = "New Value"

Result: VBA, Modify Value


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: VBA, Drop Down List, 6 Items Using the code below the first item will be removed:

ComboBox1.RemoveItem (0)

Result: VBA, Drop Down List, Remove Item


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: VBA, Drop Down List Selected Index 1 Result: VBA, Drop Down List Selected Index 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”: VBA, Drop Down List, Selected Value Result: VBA, Selected Value Result Drop Down List


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: VBA, Drop Down List, 6 Items The code below will display the item count in a drop down list:

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

Result:

VBA, Drop Down List Item Count

You can download a complete example from the link below:

See also:

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