This post will guide you how to set the default value in the dropdown list with VBA Macro in Excel. How do I create a drop down list with pre-selected value in Excel 2013/2016/2019/365.

1.Set Default Values for Drop Down List with VBA

Assuming that you have many data validation cells with drop-down list in your worksheet, and you want to set them all to a default value. Is there a way to quickly to set all of drop-down lists to a default value? Of course, yes. I found one VBA Code can be used to accomplish the result quickly, just do the following steps:

Step1: select one blank cell that you want to place drop down list.

Step2: go to Data tab in the Excel Ribbon, and click Data Validation command under Data Tools group. And the Data Validation dialog will open.

Step3: click Settings tab in the Data Validation dialog box, and choose List option from the Allow list box, and select your data as source data for drop down list.

Step4: click Error Alert tab in the Data Validation dialog box, uncheck Show error alert after invalid data is entered box. Click Ok button.

Step5: you would see that the dropdown list has been created in the selected cell.

Step6: open your excel workbook and then click on “Visual Basic” command under DEVELOPER Tab, or just press “ALT+F11” shortcut.

Step7: then the “Visual Basic Editor” window will appear.

Step8: click “Insert” ->”Module” to create a new module.

Step9: paste the below VBA code (get code from here)  into the code window. Then clicking “Save” button.

Sub DropDownListToDefault()
    Dim oCell As Range

    For Each oCell In ActiveSheet.UsedRange.Cells
        If HasValidation(oCell) Then
            oCell.Value = "'- Choose from the list -"
        End If
    Next
End Sub

Function HasValidation(cell As Range) As Boolean
    Dim t: t = Null

    On Error Resume Next
    t = cell.Validation.Type
    On Error GoTo 0

    HasValidation = Not IsNull(t)
End Function

Step10: back to the current worksheet, then run the above excel macro. Click Run button.

Step11: Let’s see the result:

Now, the selected cell will have a drop-down arrow, and the preset default value will be displayed in the cell.

2. Video: Set Default Values for Drop Down List

This tutorial video will show you how to use the Excel Data Validation feature and Visual Basic application to set dropdown list to default in Excel.

3. SAMPLE FIlES

Below are sample files in Microsoft Excel that you can download for reference if you wish.