Show or Hide Excel Worksheets by Day of Week with VBA
Question details
Automatically show the appropriate worksheet based on the day of the date entered in cell F3.

- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- A workbook has separate DOD Stocks and Weekend DOD Stocks worksheets, and the user wants the displayed sheet to follow the date in F3.
- Observed behavior
- When F3 contains a weekday date, DOD Stocks should be visible; when it contains a weekend date, Weekend DOD Stocks should be visible.
Confirm that F3 is on the worksheet where you will add the event code and that the two target sheet names match exactly. Save the workbook as an Excel Macro-Enabled Workbook (.xlsm), and make sure its structure is not protected against sheet visibility changes.
Use a Worksheet_Change event to switch worksheet visibility
Use this method when F3 is changed directly or by a macro; the code determines the day from the date in F3.
The event first makes both target sheets visible, then hides the sheet that does not match the date. This ensures that a target sheet is visible before the other is hidden.
Worksheet_Change does not run when a formula result changes through recalculation. If F3 contains a formula, use a Worksheet_Calculate event or monitor the input cell that changes instead.
In Excel, open the workbook, press Alt+F11, and in the Project Explorer double-click the worksheet that contains F3. If Project Explorer is hidden, select View > Project Explorer.
Paste this code into that worksheet's code window, not into a standard module: Private Sub Worksheet_Change(ByVal Target As Range) Dim previousEvents As Boolean Dim dateValue As Variant Dim dayNumber As Integer If Intersect(Target, Me.Range("F3")) Is Nothing Then Exit Sub previousEvents = Application.EnableEvents On Error GoTo ErrorHandler Application.EnableEvents = False ThisWorkbook.Worksheets("DOD Stocks").Visible = xlSheetVisible ThisWorkbook.Worksheets("Weekend DOD Stocks").Visible = xlSheetVisible dateValue = Me.Range("F3").Value If IsError(dateValue) Then MsgBox "Enter a valid date in F3.", vbExclamation GoTo CleanUp ElseIf Not IsDate(dateValue) Then MsgBox "Enter a valid date in F3.", vbExclamation GoTo CleanUp End If dayNumber = Weekday(CDate(dateValue), vbMonday) If dayNumber >= 6 Then ThisWorkbook.Worksheets("DOD Stocks").Visible = xlSheetHidden Else ThisWorkbook.Worksheets("Weekend DOD Stocks").Visible = xlSheetHidden End If CleanUp: Application.EnableEvents = previousEvents Exit Sub ErrorHandler: MsgBox "Could not update worksheet visibility: " & Err.Description, vbExclamation Resume CleanUp End Sub
Save the workbook as an .xlsm file and enable macros if prompted when you open it. Enter a weekday date in F3, then enter a Saturday or Sunday date, and check which sheet tab remains visible.

Work with Excel spreadsheets in WPS Office
WPS Office is a free, lightweight alternative with a familiar spreadsheet interface and Microsoft Office format compatibility for a smooth move between applications. Use Microsoft Excel for this specific VBA event unless you have verified that your WPS edition supports the macro and its behavior.

Frequently Asked Questions
Where should I put the Worksheet_Change code?
In the VBA editor, double-click the worksheet containing F3 in Project Explorer and paste the procedure into its code window. A standard module will not receive that worksheet's change event.
Why does changing a formula result in F3 not switch the sheets?
Worksheet_Change does not fire when a formula recalculates. Use a Worksheet_Calculate event or monitor the input cell that drives the formula instead.
What happens if F3 is empty or does not contain a date?
The code displays a prompt to enter a valid date and leaves both target worksheets visible. Enter a valid date directly in F3 to trigger the event again.
Why might Excel fail to hide a worksheet?
Check that the sheet names in the code match the workbook tabs exactly and that the workbook structure is not protected. Also confirm that macros are enabled and the file is saved as .xlsm.
Can I use different worksheet names?
Yes. In the worksheet event code, replace every instance of "DOD Stocks" and "Weekend DOD Stocks" with the exact names of your weekday and weekend worksheet tabs.




