VBA Drop Down List (Data Validation)

Associated Files Download Links

Excel Data Validation allows you to limit what value(s) may be entered in a cell or range. You can limit entries to positive integers, text, dates, and much more. In this tutorial, we are going to look at how to create a Data Validation Drop-Down List in a cell using VBA.
Creating a Drop Down List Using VBA
Note: An alternative to a Data Validation Drop-Down list is a ListBox object. ListBoxes can be added to Excel worksheets. ListBoxes can trigger macros that run every time a ListBox value is changed.  ListBoxes are also used in VBA Userforms.  To learn more about ListBoxes go to our ListBox tutorial.

Creating a Drop Down List Using VBA

We have the text Fruit in cell A1, and we are going to create a drop down list in cell A2, with five entries.

Creating a Drop Down List in VBA

We will use the Validation.Add method and specify that the Type parameter is xlValidateList. You can add the specific items you would like in your list using the Formula1 parameter.

The following code will create a data validation drop down list in cell A2:

The result is:

Creating a Drop Down List Using VBA

Populate a Drop Down List From a Named Range in VBA

You can use a named range containing the items, to populate a drop down list in VBA. We have the named range Animals shown below:

Using a Named Range

We have to set the Formula1 parameter equal to the named range. The following code will create a data validation drop down list in cell A7 based on the items in the named range:

The result is:

Populating A Drop Down List From a Named Range in VBA

Removing the Drop Down List

You can use the Validation.Delete method to remove the drop down list from the cell. The following code would remove the drop down list from cell A7 in the example above:

 

VBA Coding Made Easy

Stop searching for VBA code online. Learn more about AutoMacro – A VBA Code Builder that allows beginners to code procedures from scratch with minimal coding knowledge and with many time-saving features for all users!

alt text

 

Learn More!


<<Return to VBA Examples

Did you find this VBA tutorial useful? Then share it with your friends and colleagues:

You may also like some of this related content...

Ads