How to set a default value in data validation drop-down list in Excel?
Set a default value in data validation drop-down list helps others to identify the drop-down list cells easily at a glance in a worksheet. This article provides a VBA method to set a default value in data validation drop-down list in details.
Set a default value in data validation drop-down list with VBA code
Set a default value in data validation drop-down list with VBA code
As below screenshot, supposing all cells in range B2:C7 contain drop-down list, and you want to set a default value (says - Choose from the list -) in these drop-down list before choosing values from them. You can try the following VBA code.
1. Right click the sheet tab with the drop-down lists you need to set a default value, then click View Code from the menu.
2. In the Microsoft Visual Basic for Applications window, copy and paste the following VBA code into the Code window.
VBA code: set a default value in data validation drop-down list
Sub DropDownListToDefault()
Dim xCell As Range
Dim xRg As Range
Dim xAcCell As Range
Dim xScreen As Boolean
On Error Resume Next
Set xAcCell = Application.ActiveCell
Set xRg = ActiveSheet.UsedRange.SpecialCells(xlCellTypeAllValidation)
If xRg Is Nothing Then
MsgBox "No data validation drop-down lists in current worksheet", vbInformation, "Kutools for Excel"
Exit Sub
End If
xScreen = Application.ScreenUpdating
Application.ScreenUpdating = False
For Each xCell In xRg
If xCell.Validation.Type = 3 Then
If xCell.Value = "" Then xCell.Value = "'- Choose from the list -"
End If
Next
xAcCell.Select
Application.ScreenUpdating = xScreen
End SubNote: you can change the default value in the VBA code to the one you need.
6. Press the F5 key to run the code. You can see the default values are added to all drop-down list cells as below screenshot shown.
Notes:
Related articles:
- How to autocomplete when typing in Excel drop down list?
- How to create a drop down list calendar in Excel?
- How to create a searchable drop down list in Excel?
- How to create drop down list with multiple selections or values in Excel?
- How to auto populate other cells when selecting values in Excel drop down list?
Best Office Productivity Tools
Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time. Click Here to Get The Feature You Need The Most...
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
