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.
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: