Mar 14 2023 11:41 AM
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!
Mar 23 2023 04:42 AM
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.