Show or Hide Excel Command Buttons When a VBA Variable Changes
Question details
Update the visibility of Excel command buttons when a Boolean state changes.

- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- A VBA macro uses a Boolean variable to control which worksheet command buttons are visible.
- Observed behavior
- Changing the variable does not automatically trigger an event or update the buttons.
Save the workbook as an Excel Macro-Enabled Workbook (.xlsm) and enable macros if prompted. On the Developer tab, use Design Mode to check the exact names of the command buttons and confirm the worksheet tab name.
Call a refresh procedure after changing the VBA variable
Use this approach when your macros control the Boolean value and can call a procedure each time it changes.
VBA does not provide a built-in value-changed event for an ordinary Boolean variable. Declare the variable at module or public scope if multiple procedures need to access it, and explicitly refresh the buttons after each change.
In Excel, select Developer > Visual Basic, then choose Insert > Module.
Paste the following code into the standard module. Replace Sheet1 and the button names with the worksheet tab and shape names in your workbook: Public Var1 As Boolean Public Sub SetVar1(ByVal newValue As Boolean) Var1 = newValue RefreshButtons End Sub Public Sub RefreshButtons() With ThisWorkbook.Worksheets("Sheet1") .Shapes("CommandButton21").Visible = Var1 .Shapes("CommandButton22").Visible = Not Var1 End With End Sub
In each macro that changes the state, call SetVar1 True or SetVar1 False instead of assigning Var1 directly. The setter updates one button to visible and the other to hidden.

Use a worksheet Change event to update the buttons
Use a worksheet cell as the Boolean state when changes to that cell should trigger a visibility update.
Work with Excel spreadsheets in WPS Office
WPS Office is a free, lightweight Office alternative with a familiar spreadsheet interface and support for Excel formats, making it easy to move everyday spreadsheet work. Excel-specific VBA events and controls may not behave identically, so test macro-enabled workbooks before relying on them.

Frequently Asked Questions
Does Excel VBA have a value-changed event for a Boolean variable?
No. An ordinary Boolean variable has no built-in change event. Call a refresh procedure after changing it, or store the state in a worksheet cell and handle Worksheet_Change.
Where should I declare Var1 so multiple macros can use it?
In the VBA editor, choose Insert > Module and declare Public Var1 As Boolean at the top of the standard module. A module-level declaration without Public limits access to that module.
Why doesn't Worksheet_Change run when a formula result changes?
Worksheet_Change responds to changes made to cells by a user or VBA, not to formula recalculation. For a formula-driven state, consider Worksheet_Calculate and check whether the result has actually changed.
How do I find the correct command button name?
On the Developer tab, turn on Design Mode, select the button, and open Properties to check its name. Use the matching shape name in Shapes("name").Visible.
Will the VBA variable keep its value after I close Excel?
No. VBA variables are reset when the project closes or resets. Store the state in a worksheet cell if it must persist, and initialize button visibility when the workbook opens.




