Toggle an Excel Worksheet SelectionChange Macro On and Off
Question details
Use a button to toggle the behavior of an Excel worksheet's SelectionChange macro.

- 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.
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.
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.
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
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
Return to the worksheet, choose Developer > Insert > Button (Form Control), and draw the button. In the Assign Macro dialog, select ToggleSelectionFeature and click OK.
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.

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.

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.




