VBA userform drop down list from range

VBA UserForm Drop Down Lists

Jun 21, 2015 by azurous in Drop Down Lists

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.

Nội dung chính Show

VBA UserForm Drop Down Lists
Adding Items:
Inserting Items:
Modifying Items:
Removing Items:
Get Selected Index:
Get Selected Item:
Item Count:

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

Contents

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

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 stringstrValue to the the combo box.

ComboBox1.AddItem ("strValue")

Result:

Inserting Items:

Lets say there is a drop down list (combo box) with 6items:

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 ofan 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 dropdown 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 thecurrentlyselected index:

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

The code above uses a ComboBox1_Change() event handler. The event handlerexecuteswhen 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 Ive 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 aComboBox1_Change()event handler. The event handlerexecuteswhen 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: