Add a Drop Down Menu to a Cell in Excel - Data Validation Macro

Add a drop down menu or list to a cell in Excel with this free Excel macro. This is a great little macro that allows you to automatically add a drop down menu to any cell within a worksheet in Excel. This is part of the data validation features in Excel and this macro, since it is self-contained, is very easy to add to any other macro you may be using.

Both macros listed below add drop down menus or lists to cells in Excel; but, but the first one will add a drop down menu to a specific cell that you hard code into the macro whereas the second macro adds a drop down menu into any cell that has been selected before the macro is run.

To use the macros below, simply replace A1 (that appears in Range("A1").Validation) in the first macro with the cell reference of the cell in which you want the drop down menu to appear. Then, in both macros replace =$D$1:$D$3 with the range of the list which will populate or fill the drop down menu; if you are using a named range, input the name in place of that range reference.

Where to install the macro:  Module

Excel Data Validation Macro that Adds a Drop Down Menu to a Specific Cell in Excel

Sub Add_Drop_Down_Menu_Cell()

With Range("A1").Validation
 .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, _
 Formula1:="=$D$1:$D$3"
 .IgnoreBlank = True
 .InCellDropdown = True
End With

 End Sub

Excel Data Validation Macro that Adds a Drop Down Menu to a Selected Cell in Excel

Sub Add_Drop_Down_Menu_Selection()

With Selection.Validation
 .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, _
 Formula1:="=$D$1:$D$3"
 .IgnoreBlank = True
 .InCellDropdown = True
End With

 End Sub

---