Reset Excel Data Validation Cells When a Workbook Opens with VBA
Question details
The user needs to reset a range of Excel data-validation cells to a default prompt each time the workbook opens.
- Product
- Microsoft Excel VBA
- Device & OS
- not provided
- Scenario
- A workbook uses data-validation cells, and the user wants those cells cleared or reset to a standard prompt automatically when the file opens.
- Observed behavior
- The data-validation cells retain their previous values until a macro resets them during the Workbook_Open event.
Test the event procedure on a backup copy and confirm the worksheet name and target range, such as Sheet1 and G2:G100. Save the file as an XLSM workbook so the VBA code can be retained, and make sure macros are enabled for testing.
Use the Workbook_Open event to reset the validation range
Use this solution when the cells should return to the same default prompt every time the workbook opens.
The Workbook_Open event runs automatically after the workbook opens. Place the procedure in the ThisWorkbook module rather than in a standard module, and update the worksheet and range references to match your file.
Open the workbook in Excel, press Alt+F11, and locate the workbook under the Project pane.
Double-click ThisWorkbook under Microsoft Excel Objects, then paste the Workbook_Open procedure into the code window.
Enter this code and change the worksheet name or range as needed: Private Sub Workbook_Open() Application.ScreenUpdating = False Worksheets("Sheet1").Range("G2:G100").Value = "PLEASE SELECT" Application.ScreenUpdating = True End Sub
Return to Excel, choose File > Save As, select Excel Macro-Enabled Workbook (*.xlsm), close and reopen the file, and select Enable Content if Excel displays a macro warning.
Manage Excel-compatible workbooks with a lightweight WPS Office alternative
WPS Office provides a free, lightweight spreadsheet experience with a familiar interface and compatibility with common Microsoft Office formats. Use it to open, review, and migrate Excel workbooks while keeping everyday spreadsheet editing simple.
- 1. Install WPS Office: Download and install WPS Office, then open WPS Spreadsheets from the main application screen.
- 2. Open the workbook: Choose Menu > Open, select the Excel workbook, and review the target worksheet and data-validation cells.
- 3. Edit and save a compatible copy: Make supported spreadsheet changes, then choose Menu > Save As and select an Excel-compatible format such as XLSX when a macro-free copy is appropriate.
Frequently Asked Questions
Where should the Workbook_Open VBA code be placed?
Place it in the ThisWorkbook module in the VBA editor. Press Alt+F11, expand the workbook project, and double-click ThisWorkbook under Microsoft Excel Objects.
Why does the Workbook_Open macro not run when the file opens?
The workbook may be saved as XLSX instead of XLSM, or macros may be disabled. Save it as an Excel Macro-Enabled Workbook and choose Enable Content when prompted.
Does resetting the cell value remove data validation?
No. Assigning a value such as PLEASE SELECT changes the cell contents but leaves the existing data-validation settings in place.
Can I reset a different worksheet or range?
Yes. Replace Worksheets("Sheet1") with the correct sheet name and change Range("G2:G100") to the required cell range. Use the exact worksheet name, including spaces.
How can I reset cells to blank instead of a prompt?
Replace the assigned text with an empty string, such as Worksheets("Sheet1").Range("G2:G100").Value = "". Test the change on a copy before using it in the original workbook.
