VBA & Macro Problems

How to Temporarily Override and Restore Cell Values Using Excel VBA

Khadija Khan Sep 30, 2026 868 views

Question details

The user needs to temporarily change a cell's value based on a dropdown selection and restore its original value when a different option is chosen, without triggering recursive VBA events.

How to Temporarily Override and Restore Cell Values in Excel VBA

Product: Excel
Device & OS: not provided
Scenario: A worksheet requires setting a concentration cell to zero when 'water' is selected from a data-validation list, and restoring the original concentration when another fluid is selected.
Observed behavior: The goal is to automatically switch and restore cell values using VBA's Worksheet_Change event while avoiding infinite event loops and preserving existing macros.

Before you start

Before editing your VBA code, ensure you save a copy of your workbook as a Macro-Enabled Workbook (.xlsm) and identify any existing Worksheet_Change event procedures to avoid duplicate event errors.

Solution 1Recommended

Implement the Worksheet_Change Event with Event Management

Use this method to detect dropdown changes and temporarily overwrite the target cell while disabling events to prevent infinite loops.

To prevent recursive calls when modifying a cell via VBA, you must temporarily disable application events.

If your worksheet already contains a Worksheet_Change procedure, you must combine this new logic into the existing procedure rather than creating a second one.

Open the VBA Editor

Press Alt + F11 to open the Visual Basic for Applications editor, then double-click the specific Sheet name in the Project Explorer on the left.

Declare variables and set target ranges

Inside the Worksheet_Change event, declare a Range variable (e.g., `keyCell`) to monitor the dropdown list cell (e.g., A1).

Check for intersection and value

Use `If Not Application.Intersect(keyCell, Target) Is Nothing Then` to verify the dropdown was changed, then check if its value equals 'water'.

Store the original value

Before overwriting, store the current concentration cell's value (e.g., B1) in a hidden worksheet cell or a persistent variable so it can be restored later.

Disable events and update cell

Execute `Application.EnableEvents = False`, change the concentration cell's value to 0, and immediately restore event handling with `Application.EnableEvents = True`.

Write the restore logic

In the `Else` block, retrieve the stored original value and place it back into the concentration cell using the same EnableEvents toggle structure.

Implement the Worksheet_Change Event with Event Management

Storing Values: Using a hidden cell on the worksheet is more reliable for storing the previous value than a VBA variable, as variables lose their data when the session ends or code resets.

Run VBA Macros Seamlessly in WPS Office

WPS Office fully supports VBA and Macros, allowing you to run, edit, and create complex automation scripts just like in Microsoft Excel.

1. Open WPS Spreadsheet: Launch WPS Office and open your Macro-Enabled Workbook (.xlsm).
2. Enable Developer Tools: Navigate to the 'Developer' tab in the top ribbon to access macro settings.
3. Launch the VBA Editor: Click on 'Visual Basic' or press Alt + F11 to edit your Worksheet_Change events effortlessly.

Full compatibility with Microsoft Excel VBA code and .xlsm formatsBuilt-in VBA editor for writing and debugging macrosLightweight application with high performance for heavy datasetsFree to download and use for daily spreadsheet tasks

microsoft office alternative - wps office

Frequently Asked Questions

Why does my Excel freeze when changing a cell value using VBA?

This usually happens due to an infinite loop known as recursive event calling. When your VBA code changes a cell, it triggers the Worksheet_Change event again. To fix this, always use `Application.EnableEvents = False` before changing a value in your code, and set it back to `True` immediately after.

Can I have multiple Worksheet_Change events on the same sheet?

No, Excel only allows one Worksheet_Change procedure per worksheet. If you need to monitor multiple different cells or ranges, you must combine the logic within the single existing Worksheet_Change event using `If` or `Select Case` statements.

Where is the best place to store temporary values in Excel VBA?

For data that needs to persist between sessions or after a code reset, storing the value in a hidden worksheet cell or a Very Hidden sheet is the safest method. VBA variables are cleared when the file is closed or an error resets the project.

Excel & Data Problems