Simple VBA macro to create drop down list from selected cells in your excel sheet.
- Select the cells for which you want to create drop down list
- Run the macro. Drop down list gets created in column next to the selected cells (with down arrow)
- Click on down-arrow button to see the list
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