VBA & Macro Problems

Show or Hide Excel Command Buttons When a VBA Variable Changes

Amos GikundaAmos Gikunda Nov 20, 2026 869 views

Question details

Update the visibility of Excel command buttons when a Boolean state changes.

Show or Hide Excel Command Buttons When a VBA Variable 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.
Before you start

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.

Solution 1Recommended

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.

1
Open the VBA editor

In Excel, select Developer > Visual Basic, then choose Insert > Module.

2
Add the state and refresh procedures

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

3
Update the state through the setter

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.

Call a refresh procedure after changing the VBA variable
Initialize the buttons: A VBA variable does not retain its value between Excel sessions. Set the desired state and call RefreshButtons when appropriate, such as from a Workbook_Open procedure, or store the state in a worksheet cell.
Free Microsoft Office alternative

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.

Open and edit common Microsoft Excel formats, including XLSX and XLS.Use familiar spreadsheet tools to work with cells and worksheet data.Move everyday spreadsheet work to a lightweight, free Office suite.
microsoft office alternative - wps office

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.