How to Set Default Values for Drop Down List in Excel

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.

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.

set default value for drop down list1

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.

set default value for drop down list2

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.

set default value for drop down list3

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

set default value for drop down list4

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

set default value for drop down list5

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

Get the position of the nth using excel vba1
Step7: then the “Visual Basic Editor” window will appear.

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

export each sheet to csv2

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

set default value for drop down list6

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.

set default value for drop down list7

Step11: Let’s see the result:

set default value for drop down list8

You might also like:

Comments

So empty here ... leave a comment!

Leave a Reply

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

Sidebar