Add Drop Down List To Selection Excel VBA Macro

In this post we see how to add a drop-down list to selected cells with Excel VBA. This can easily be done manually in Excel going to Data - Data Validation and selecting the List type. The macro automates that process and allows to repeat as many times as needed, for as many sheets or workbooks as wished. Furthermore, using a macro allows to create dynamic lists depending on sheet data in Excel. 

Macro/VBA code:

 

  Sub AddDropDownToSelection()

      Dim ValList As String

      ValList = "Beginner, Intermediate, Advance"

      With Selection.Validation

          .Delete

          .Add Type:=xlValidateList, Formula1:=ValList

           

          'other useful properties (optional)

          .InputTitle = "Level"

          .InputMessage = "Select the level of proficiency"

          .ShowInput = True

          .ErrorTitle = "Level not available"

          .ErrorMessage = "Select one of the available level options"

          .ShowError = True

      End With

  End Sub

   

Macro explained:

• We add the values of the drop-down list to a String variable. It must be a comma-delimited list of text values.
• Then we delete any previous validation in the selected range before adding the new drop-down (validation), in case there was any validation already there; otherwise it could prompt an error.
• Now we add the drop-down list to the selected range by choosing the validation type xlValidateList and setting the Formula1 parameter to the validation list variable. In VBA terminology, that uses the Add method of the Validation property for a Range object. We can also write the comma-delimited list directly, without using any variable. 
• Alternatively, we can have a worksheet reference with values for the drop-down list. In such case the sheet and range address are given (e.g. "=Sheet1!H1:H3"). Note that the worksheet reference is not a VBA object reference, it works as any other worksheet or cell reference in Excel.
• There are some additional properties of the Validation object that help customize the drop-down list by adding a title and description, and to choose whether to show a specific message when an error occurs.

This is how we add a drop down list in Excel with VBA macros.

Other examples:

• How to Add Checkboxes To Selection with Excel VBA macros
• How to Download Email Attachments with Excel VBA macros
• How to Get Last 10 Rows From Google Sheets with Excel VBA macros