Forum Discussion

MichaelShokhin's avatar
MichaelShokhin
Copper Contributor
Mar 14, 2023

Excel VBA Conditional Formatting; Hide worksheet based on day of week

VBA newbie here. I'm attempting to have 2 worksheets responding to formulas that I believe should be used with VBA.    Context: I have a workbook that contains a fill-in checklist that has names, a...
Formulas and Functions
Macros and VBA
SnowMan55's avatar
SnowMan55
Bronze Contributor
Mar 23, 2023

MichaelShokhin 

As I understand your situation, your workbook has at least three worksheets.  I'll refer to the worksheet that contains the "Date of death" and "Day of week" cells as WS1.  So you will want a VBA event handler to respond to changes in the WS1 worksheet; this event handler is called Worksheet_Change.  Such code will be located here:

 

 

That procedure could include this code (and more, if you find you have other work to do there):

    Dim strDayOfWeek    As String
    Dim strMessage  As String
    
    '----   If a change was made to the "Date of death"...
    If Intersect(Target, Me.Range("F3")) Is Nothing Then
        '...any change in the worksheet did not involve cell F3.
        '   (Well, technically, it could have, if F3 contains a
        '   formula.  But the change was not made there, so you'll
        '   have to monitor a different cell.)
    Else
        '...the value in cell F3 changed.  Capture the possibly-
        '   different day-of-week value.
        strDayOfWeek = Me.Range("F4").Value
        '  --   Make both related worksheets visible, pending the
        '       value-related change of visibility.
        Application.Sheets("DOD Stocks").Visible = True
        Application.Sheets("Weekend DOD Stocks").Visible = True
        '  --   Depending upon the value...
        Select Case LCase$(strDayOfWeek)
            Case "sunday", "saturday"
                Application.Sheets("DOD Stocks").Visible = False
            Case "monday", "tuesday", "wednesday", "thursday", "friday"
                Application.Sheets("Weekend DOD Stocks").Visible = False
            Case Else
                strMessage = "Unexpected value for day of week in F4: " _
                        & strDayOfWeek
                Call MsgBox(strMessage, vbExclamation + vbOKOnly, "")
        End Select
    End If

(Of course, you can decide whether a message should be displayed if cell F4 ends up having something other than the name of a weekday.)

 

As always, including an "Option Explicit" statement at the top of each code module is strongly recommended.

Resources