VBA & Macro Problems

Fix an Excel VBA Function That Stops When Changing Other Cells

Natalie TaylorNatalie Taylor Nov 13, 2026 868 views

Question details

The user needs Excel VBA to update cells such as L8 or M8 when a condition is met, while keeping the previously written value unchanged until the condition becomes true again.

Fix an Excel VBA Function That Stops When Changing Other Cells
Product
Microsoft Excel VBA
Device & OS
not provided
Scenario
A custom worksheet function checks input cells and attempts to write a result into another cell.
Observed behavior
The worksheet function stops when it attempts to modify other cells, without displaying a clear error.
Before you start

Identify the input cells, destination cells, and exact condition that should trigger an update. Open the workbook in desktop Excel with VBA support, save it as an Excel Macro-Enabled Workbook (.xlsm), and enable macros only if you trust the file.

Solution 1Recommended

Use Worksheet_Change to update and preserve values in other cells

Use a worksheet event when changes to input cells should write a value into another cell and leave that value unchanged between qualifying updates.

A VBA function called from a worksheet formula cannot directly write to other cells. Move those writes into a worksheet event procedure instead.

The example below monitors C2, C3, L2, and L3. It copies K8 into L8 when C2 equals C3 and L2 equals L3. These comparisons are illustrative; replace them with your actual IF condition.

1
Open the worksheet code module

Right-click the worksheet tab containing the inputs and choose View Code. Place the event procedure in this worksheet module, not in a standard module.

2
Add the event procedure

Paste this code into the worksheet module. If Worksheet_Change already exists there, merge this logic into that procedure rather than adding a second one: Private Sub Worksheet_Change(ByVal Target As Range) If Intersect(Target, Me.Range("C2:C3,L2:L3")) Is Nothing Then Exit Sub On Error GoTo HandleError Application.EnableEvents = False If Me.Range("C2").Value = Me.Range("C3").Value And _ Me.Range("L2").Value = Me.Range("L3").Value Then Me.Range("L8").Value = Me.Range("K8").Value End If CleanExit: Application.EnableEvents = True Exit Sub HandleError: MsgBox "Update failed: " & Err.Description, vbExclamation Resume CleanExit End Sub

3
Customize the trigger and destination

Replace the comparisons in the If statement with your real condition. Change Me.Range("L8") to the required destination, or add a separate assignment for M8 inside the same If block. Add any other directly edited trigger cells to the Intersect range.

4
Test the retained value

Return to Excel and edit one of the monitored cells so the condition is true. Confirm that L8 receives the value from K8. Then make the condition false and confirm that L8 keeps its previously written value; do not add an Else branch that clears L8.

Formula recalculation does not trigger Worksheet_Change: This event responds to cell edits and certain external updates, not to changes caused only by formula recalculation. If the monitored values change through formulas, consider Worksheet_Calculate with a guard against repeated updates. The example writes on each qualifying edit; detecting only a false-to-true transition requires tracking the previous condition.
Free Microsoft Office alternative

Use WPS Spreadsheet for everyday Excel-compatible workbook editing

WPS Office is a free, lightweight alternative with a familiar spreadsheet interface and support for Excel .xlsx and .xls files, making everyday workbook migration straightforward. It does not fix Excel's worksheet UDF restrictions; verify VBA support in your specific WPS edition before relying on event-driven automation.

Open and edit Microsoft Excel .xlsx and .xls workbooks.Use ordinary IF formulas for condition-based results that do not require retained values.Work with a familiar spreadsheet interface for an easier transition from Excel.Use a free, lightweight office suite for everyday spreadsheet tasks.
microsoft office alternative - wps office

Frequently Asked Questions

Why does my VBA function stop at Range("L8").Value = something?

When Excel calls a VBA function from a worksheet formula, that function cannot directly change another cell. The assignment can cause the formula evaluation to fail. Return the result by assigning it to the function name, or move the cell-writing code into a worksheet event or a Sub procedure.

Where do I put Worksheet_Change code?

Right-click the relevant worksheet tab and choose View Code. Paste the procedure into that worksheet's code module. A Worksheet_Change procedure in a standard module will not automatically respond to worksheet edits.

How can L8 keep its value until the condition is true again?

Write to L8 only inside the event procedure's true-condition branch, and omit any Else branch that clears or replaces it. This preserves the value while the condition is false. If you want updates only when the condition switches from false to true, add logic to store and compare the previous condition.

Why disable Application.EnableEvents before writing to cells?

A cell write from Worksheet_Change can trigger another change event and cause recursion. Set Application.EnableEvents = False before writing, then restore it to True on both normal and error exits. If events were left disabled, open the Visual Basic Editor's Immediate window and run Application.EnableEvents = True.

What if my input cells change because formulas recalculate?

Worksheet_Change does not fire solely because a formula result changes. Use Worksheet_Calculate in the worksheet module when recalculation must drive the update, and track previous input values or the previous condition to avoid unnecessary repeated writes.