How to Use VBA to Show or Hide a Worksheet Based on Cell Values
Question details
The user needs to use a VBA macro to automatically show or hide a specific worksheet dynamically based on the values entered into designated cells, but the current code is failing to execute upon changes.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Automating spreadsheet visibility so that when a user updates certain cells (like C2 and C34), a dependent worksheet automatically reveals itself or hides.
- Observed behavior
- The worksheet remains visible incorrectly because the VBA condition does not trigger when the cell values change, or the cell references within the code are ambiguous.
Ensure your workbook is saved as a Macro-Enabled Workbook (.xlsm) and that macro execution is enabled in your security settings before attempting to run VBA code.
Use the Workbook_SheetChange Event
Place your visibility logic inside the Workbook_SheetChange event so the VBA macro runs automatically whenever a cell value is modified.
If your code relies on a standard module, it will not execute automatically when data changes. Utilizing Excel's built-in change events allows the software to constantly monitor for specific edits and trigger the visibility rules immediately.
Press Alt + F11 on your keyboard to open the Visual Basic for Applications (VBA) editor.
In the Project Explorer pane on the left side, double-click on 'ThisWorkbook' to open its dedicated code window.
Paste the following event procedure to evaluate target cells: Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range) Const YOUR_SHEET As String = "SHEET1" If UCase(Sh.Name) = YOUR_SHEET Then With ActiveSheet If .Range("$C$2") & "" = "Target Value 1" And .Range("$C$34") & "" = "Target Value 2" Then Sheets("Target Sheet Name").Visible = -1 Else Sheets("Target Sheet Name").Visible = 0 End If End With End If End Sub
Replace 'SHEET1', 'Target Value 1', 'Target Value 2', and 'Target Sheet Name' with your exact sheet names and desired cell strings.
Debug Cell References via the Immediate Window
Use the VBA debugger's Immediate Window to verify that your cell string values match your logic exactly and test worksheet visibility settings.
Use WPS Spreadsheet to Run Macros and Automate Tasks
WPS Spreadsheet features robust built-in support for VBA and macros, allowing you to easily configure worksheet events like Workbook_SheetChange to automate visibility and complex logical conditions.
- 1. Open your Workbook: Launch WPS Spreadsheet and open your Macro-Enabled Workbook (.xlsm).
- 2. Access the VBA Editor: Navigate to the 'Developer' tab on the top ribbon and click on 'Visual Basic' to open the VBA environment.
- 3. Apply Automation Logic: Locate 'ThisWorkbook' in the Project Explorer, paste your visibility macro code, and save to enable automatic worksheet hiding and showing.
Frequently Asked Questions
Why is my VBA code not triggering when a cell value changes?
This usually happens if the macro is placed in a standard module rather than the required event module (like Worksheet_Change or Workbook_SheetChange). It can also occur if Excel's Application.EnableEvents property was accidentally set to False.
What is the difference between setting Visible to 0 and -1 in VBA?
In VBA, setting a sheet's Visible property to -1 (xlSheetVisible) makes the sheet visible. Setting it to 0 (xlSheetHidden) hides the sheet, but users can still manually unhide it by right-clicking the sheet tabs.
How do I make a worksheet completely hidden so users cannot unhide it?
You can set the sheet's Visible property to 2 (xlSheetVeryHidden). When a sheet is Very Hidden, it will not appear in the standard unhide menu and can only be made visible again by using VBA code.
Does the Worksheet_Change event work if the cell value is updated by a formula?
No, the Worksheet_Change event only triggers when a user or external script actively modifies a cell. If the target cell is updated via a formula calculation, you must use the Worksheet_Calculate event instead to monitor the change.
