VBA macro to create drop down list from selected cells [Excel]

Simple VBA macro to create drop down list from selected cells in your excel sheet.

  1. Select the cells for which you want to create drop down listvba-macro-drop-down-list
  2. Run the macro. Drop down list gets created in column next to the selected cells (with down arrow)macro-to-create-drop-down-list
  3. Click on down-arrow button to see the list

vba-macro-drop-down-list-excel

Notes: You can copy & paste the drop-down list cell (2nd step) anywhere in your sheet.But, if you delete the data from selected cells(1st step), you won’t see data in drop down list. For example, if you delete item1, you will see only item2,item3 and item4 in the drop down list.

VBA macro to create drop down list

Sub createDropDownList()
 Set SelRange = Selection
 ActiveWorkbook.Names.Add Name:="dropDownName", RefersToR1C1:=SelRange
 ActiveWorkbook.Names("dropDownName").Comment = ""
 ActiveCell.Offset(0, 1).Value = "drop-down"
 ActiveCell.Offset(0, 1).Select
 With Selection.Validation
 .Delete
 .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
 xlBetween, Formula1:="=dropDownName"
 .IgnoreBlank = True
 .InCellDropdown = True
 .InputTitle = ""
 .ErrorTitle = ""
 .InputMessage = ""
 .ErrorMessage = ""
 .ShowInput = True
 .ShowError = True
 End With
End Sub

About the Author

SRINI S

A passionate blogger. Love to share solutions and best practices on wordpress hosting, issues and fixes, excel VBA macros and other apps

Leave a Reply

Your email address will not be published. Required fields are marked *