There are three methods to read the selected values from a combobox in VBA. Please go through the following blog to know how you can do it.

Method 1:

Consider, we have a dropdown list having days mentioned in it. The value selected in the dropdown is ‘Sunday’. Now, we will try to read the selected value from the dropdown list using Excel VBA.

To read the selected day from the dropdown list into a variable, we can use the following snippet.

In the above code, we are creating an object reference for the dropdown “Drop Down 1”.

If you observe the code, we are using OLEFormat.Object.  OLEFormat property is used when we are working with shapes (ComboBox, ListBox and so on) , inline shapes, or fields to return the OLEFormat object.

‘ddval’ is the variable that stores the value of the selected dropdown.

dd.List(dd.ListIndex)

Here, List returns an item from List index. List index is an expression that returns index of the objects in the list.

As we can see from the image above the variable ‘ddval’ has value ‘Sunday’.

Method 2:

In this method we will use ‘ListFillRange’. This property ‘ListFillRange’ reads the contents of every cell in the range designated for the list and inserts the cell values into the list box. Before fetching the selected dropdown value, we have to assign the range of dropdown values to property ‘ListFillRange’

Say, if the list of options are in Column Q starting from Q1 to Q10, then we define the listfillrange for the dropdown.

Once the ListFillRange is assigned, the selected dropdown value is fetched as below. ‘ddval’ is the variable that stores the dropdown value from the dropdown list.

Method 3:

In this method, we will use cell link to read the dropdown value to the variable.

Based on the selection in the dropdown list the cell link value in ‘I1’ changes. The cell link value in ‘I1’ can be used to read the selected dropdown value.

Offset method:

Here, we use ‘ListFillRange’ to identify the range and assign its value to the ‘rr’ variable. ‘x’ is the variable used to find the offset row location.

Based on ‘x’ value and by using offset function, the value of the dropdownlist is found as below

We are taking the dropdown value into the ‘ddval’ variable as shown in the image below.

This is how we get combobox selected value in VBA. If you have any queries then please contact our Excel Expert here.