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.
You may interest in:
- Reuse Anything: Add the most used or complex formulas, charts and anything else to your favorites, and quickly reuse them in the future.
- More than 20 text features: Extract Number from Text String; Extract or Remove Part of Texts; Convert Numbers and Currencies to English Words.
- Merge Tools: Multiple Workbooks and Sheets into One; Merge Multiple Cells/Rows/Columns Without Losing Data; Merge Duplicate Rows and Sum.
- Split Tools: Split Data into Multiple Sheets Based on Value; One Workbook to Multiple Excel, PDF or CSV Files; One Column to Multiple Columns.
- Paste Skipping Hidden/Filtered Rows; Count And Sum by Background Color; Send Personalized Emails to Multiple Recipients in Bulk.
- Super Filter: Create advanced filter schemes and apply to any sheets; Sort by week, day, frequency and more; Filter by bold, formulas, comment...
- More than 300 powerful features; Works with Office 2007-2019 and 365; Supports all languages; Easy deploying in your enterprise or organization.
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 Sub
Note: 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.
1. The default value will disappear after selecting value in the drop-down list.
2. If there has no data validation drop-down lists in current worksheet, you will get the below prompt box.
- 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?