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, addresses, dates, etc. One of these cell values is the date of death, and another cell has values for the day of the week they died. 

 

Currently, the "Day of week" cell has a formula pulling the date, and outputting the day of week in text

 

Ex: F3 = 11/05/1998 -> F4 = Thursday. F4 formula is [=TEXT(F3,"dddd")].

 

I have 2 worksheets within the workbook that alternate usage based on what day of the week it is. 

Worksheet "DOD Stocks" and "Weekend DOD Stocks"

 

I'd like to use VBA to make "DOD Stocks" hidden when the day of death is a Sunday or Saturday.

Alternatively, I'd also like to have "Weekend DOD Stocks" hidden when the day of death is a weekday.

 

Would appreciate your responses!

Formulas and Functions
Macros and VBA

1 Reply

Sort By
  • 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