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

Add to Favorites
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










How to Install the Macro
  1. Select and copy the text from within the grey box above.

  2. Open the Microsoft Excel file in which you would like the Macro to function.

  3. Press "Alt + F11" - This will open the Visual Basic Editor - Works for all Excel Versions.  Or For other ways to get there, Click Here.

  4. On the new window that opens up, go to the left side where the vertical pane is located. Locate your Excel file; it will be called VBAProject (YOUR FILE'S NAME HERE) and click this.

  5. If the Macro goes in a Module, Click Here, otherwise continue to Step 8.

  6. If the Macro goes in the Workbook or ThisWorkbook, Click Here, otherwise continue to Step 8.

  7. If the Macro goes in the Worksheet Code, Click Here, otherwise continue to Step 8.

  8. Close the Microsoft Visual Basic Editor window and save the Excel file. When you close the Visual Basic Editor window, the regular Excel window will not close.

  9. You are now ready to run the macro.


Excel Forum