VBA & Macro Problems

Toggle an Excel Worksheet SelectionChange Macro On and Off

Steve KSteve K Nov 14, 2026 868 views

Question details

Use a button to toggle the behavior of an Excel worksheet's SelectionChange macro.

Toggle an Excel Worksheet SelectionChange Macro On and Off
Product
Microsoft Excel
Device & OS
not provided
Scenario
A worksheet event changes Excel's calculation mode when the user selects a different cell, and the user wants to switch that behavior using a Boolean variable.
Observed behavior
The toggle reverses the Boolean value; the next selection change sets calculation to automatic when the feature is enabled and manual when it is disabled.
Before you start

Open the workbook in a desktop version of Excel that supports VBA, and save it as an Excel Macro-Enabled Workbook (.xlsm). Enable macros if prompted, and show the Developer tab if it is hidden.

Solution 1Recommended

Toggle the worksheet event behavior with a Boolean variable and button

Use this approach to switch the calculation-mode behavior on or off without disabling all Excel events.

1
Add the worksheet code

Go to Developer > Visual Basic. In Project Explorer, double-click the target worksheet and add this code to its module: Private testVar As Boolean Public Sub ToggleBoolean() testVar = Not testVar End Sub Private Sub Worksheet_SelectionChange(ByVal Target As Range) If testVar Then Application.Calculation = xlCalculationAutomatic Else Application.Calculation = xlCalculationManual End If End Sub

2
Add a button macro

In the Visual Basic editor, choose Insert > Module and add the following procedure. Replace Sheet1 with the target worksheet's code name shown in Project Explorer: Public Sub ToggleSelectionFeature() Sheet1.ToggleBoolean End Sub

3
Assign the macro to a button

Return to the worksheet, choose Developer > Insert > Button (Form Control), and draw the button. In the Assign Macro dialog, select ToggleSelectionFeature and click OK.

4
Test the toggle

Click the button, then select a different cell. The first click changes the default False value to True, so the selection event sets calculation to automatic; clicking again makes the next selection event set it to manual.

Toggle the worksheet event behavior with a Boolean variable and button
Calculation mode affects the Excel application: Application.Calculation is not limited to this worksheet. The button changes the variable immediately, but the calculation mode changes only on the next selection event. If the worksheet already has a Worksheet_SelectionChange procedure, integrate the If block into that procedure rather than adding a second one.
Free Microsoft Office alternative

Work with Excel spreadsheets in WPS Office

WPS Office is a free, lightweight alternative with a familiar spreadsheet interface and support for common Excel formats, making it easier to move everyday spreadsheet work. It does not fix Excel's VBA event behavior; check macro support in your WPS version before relying on this toggle.

Open and edit spreadsheets using familiar worksheet controls.Compatible with common Microsoft Excel formats, including XLSX and XLS.Continue everyday spreadsheet work with a lightweight office suite.Move common spreadsheet files between Excel and WPS Office.
microsoft office alternative - wps office

Frequently Asked Questions

Does the button disable all worksheet events?

No. It reverses a worksheet-level Boolean used by the SelectionChange procedure. It does not change Application.EnableEvents or disable other Excel events.

Why doesn't the calculation mode change as soon as I click the button?

The button only changes the Boolean value. Select a different cell to trigger Worksheet_SelectionChange and apply the corresponding calculation mode.

What should I do if the worksheet already has a SelectionChange procedure?

Open the worksheet module through Developer > Visual Basic and add the Boolean test inside the existing Worksheet_SelectionChange procedure. A worksheet module cannot have two procedures for the same event.

Will the toggle state be saved when I close the workbook?

No. The worksheet-level Boolean is reset when the VBA project resets or the workbook is reopened. It starts as False unless you add code to initialize it differently.

Can I run this VBA toggle in Excel for the web?

No. Excel for the web cannot run VBA macros. Open the macro-enabled workbook in a desktop version of Excel that supports VBA to use the button.