VBA & Macro Problems

Set a Default Cell Value with Data Validation in Excel

Chanuka GeekiyanageChanuka Geekiyanage Nov 14, 2026 868 views

Question details

Set a default value in a cell while allowing users to replace it with a value permitted by data validation.

Set a Default Cell Value While Allowing Data Validation in Excel
Product
Microsoft Excel
Device & OS
not provided
Scenario
A validated cell should display a default value but remain editable by users.
Observed behavior
A formula in the cell is replaced when a user manually enters another value.
Before you start

Choose a default that meets the cell's data validation rules. Use a desktop version of Excel that supports VBA and confirm that macros can be enabled for the workbook.

Solution 1Recommended

Fill a Blank Validated Cell Using a Worksheet Event

Use VBA to insert a default when the cell is blank, while keeping its data validation in place.

The event below checks A1 whenever the worksheet selection changes. If A1 is blank, it inserts the default; otherwise, it leaves the existing value unchanged.

A formula alone cannot remain in the same cell after a user manually replaces its result.

1
Set up data validation

Select A1, open Data > Data Validation, and configure the allowed values. Make sure the default you plan to use is permitted.

2
Open the worksheet module

Press Alt+F11 to open the VBA editor. In Project Explorer, double-click the worksheet containing A1.

3
Add the event code

Paste this code into the worksheet module, replacing "default value" with your permitted default: Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Range("A1").Value = "" Then Range("A1").Value = "default value" End If End Sub

4
Save and test

Save the workbook as an Excel Macro-Enabled Workbook (.xlsm), enable macros when prompted, and change the selection on the worksheet. Replace the default in A1 with an allowed value to verify that it remains in place.

Fill a Blank Validated Cell Using a Worksheet Event
When the default returns: If A1 is cleared, the default is inserted the next time the worksheet selection changes. The event does not run until that selection change occurs.
Free Microsoft Office alternative

Manage Validated Spreadsheets with WPS Office

WPS Office is a free, lightweight Office alternative with a familiar interface and Microsoft Office format compatibility for an easy transition. Use WPS Spreadsheets to work with validated cells; the Excel VBA event above is specific to Excel and may not work the same way in WPS.

Work with spreadsheet data validation and editable cell valuesOpen and edit Microsoft Excel XLSX filesUse a familiar spreadsheet interface for an easier transitionAccess a free, lightweight office suite
microsoft office alternative - wps office

Frequently Asked Questions

Can a formula provide a default and still be edited in the same cell?

No. Typing a replacement directly into the cell overwrites its formula. A worksheet event can instead insert a static default when the cell is blank.

Will the default overwrite a value the user entered?

No. The example checks whether A1 is blank before inserting the default, so it leaves a nonblank user entry unchanged.

Why is the default not appearing immediately?

The example runs when the worksheet selection changes. Click another cell to trigger it, and check that macros are enabled in the workbook.

What happens if the default is not allowed by data validation?

Choose a default that meets the rules under Data > Data Validation. VBA can write values without triggering the same validation checks as manual entry.

Where should I put the VBA code?

Open the VBA editor with Alt+F11 and paste the code into the module for the worksheet containing the cell, not into a standard module.