[Excel VBA] Mastering ComboBoxes (DropDowns) on Sheets! Major Properties Explained

2025年12月29日

Introduction

The “Form Control” ComboBox (known as the DropDown object in VBA) that can be placed on Excel sheets is a convenient tool for presenting options to users while saving space.

To manipulate this ComboBox freely with VBA, understanding its major properties is essential. This article explains the four most important properties for operating ComboBoxes on a sheet, with sample code for each.

List: Sets the items in the dropdown.
Value / ListIndex: Gets or sets the number of the selected item.
DropDownLines: Sets the number of lines displayed in the list.

1. List Property

This sets or retrieves the list of items displayed in the dropdown. The easiest way to set this is by directly assigning a VBA array.

Sample Code

Sub PopulateComboBox()
    Dim targetDropDown As DropDown
    ' Get the ComboBox named "CategoryDropDown"
    Set targetDropDown = ActiveSheet.DropDowns("CategoryDropDown")
    
    ' Assign an array created with the Array function to the .List property
    targetDropDown.List = Array("Category A", "Category B", "Category C", "Category D")
End Sub

Explanation: By simply assigning an array created with the Array function to the .List property, the list items are set all at once.

2. Value / ListIndex Properties

These properties get or set the index number (starting from 1) of the item selected by the user.

On sheet-based ComboBoxes, .Value and .ListIndex function identically. You can use either.
If nothing is selected, it returns 0.

Sample Code

Sub SelectAndGetComboBoxItem()
    Dim targetDropDown As DropDown
    Set targetDropDown = ActiveSheet.DropDowns("CategoryDropDown")
    
    ' Set the 3rd item (Category C) as selected
    targetDropDown.Value = 3
    
    ' Get the index number of the currently selected item
    MsgBox "Item number " & targetDropDown.ListIndex & " is selected."
End Sub

Note: While ListIndex starts from 0 in UserForm ComboBoxes, it starts from 1 in sheet-based ComboBoxes (Form Controls).

3. DropDownLines Property

This sets the number of item lines displayed at once when the user clicks the dropdown list.

Sample Code

Sub SetDropDownLines()
    Dim targetDropDown As DropDown
    Set targetDropDown = ActiveSheet.DropDowns("CategoryDropDown")
    
    ' Set the dropdown list to display 8 lines
    targetDropDown.DropDownLines = 8
End Sub

Explanation: The default is usually 8 lines. Adjusting this value to match the number of items in your list reduces scrolling effort and provides a more user-friendly list.

Summary

This article explained the major properties for manipulating ComboBoxes (DropDowns) on a sheet using VBA.

.List: Use an Array to set the list of options.
.Value or .ListIndex: Manipulate the index number of the selected item (1-based).
.DropDownLines: Adjust the number of display lines for the list.

Combining these properties allows for the creation of interactive sheets where ComboBox contents change dynamically or processing is implemented based on user selection.

よかったらシェアしてね！