Event code to populate message upon workbook open at specified dates

[bh24524]

Hi, so as the title says, I'm looking for an event code that will give me a reminder that certain entries need updated in a particular system. At the beginning of every month, we have to update hour balances in our attendance tracker for those recently completing a probationary period. The way this attendance policy works on a probationary basis is they will earn hours until February. These hours are used to cover unexcused absences or unapproved time taken away from work. This policy is effective 6/2/24 and anyone completing probation on or after that date is subject to the policy. In March, their record is wiped clean and they start the next attendance yearwith a full hour balance - no more pro-ration. Here is a picture of how we currently track it:

So by the above, row 131 with 6/11/24 is actually the first row where someone falls under the new pro-rated policy. The formula in column I is a recent formula obtained that updates automatically on the 1st of every month via formula pulling from a table. (Thank you to felixstraube for that)

What I'm looking to do is for the first 5 days of every month, have a pop-up message appear when I open the work book that will tell me there are updates to be made to hour balances. If it could even list names of people and their balances, that would be awesome! The names are located in column C, so they ultimately start at C131 and on as well as I131 and on. So something like this:

"Reminder, the following employees need pro-rated balances updated in the Attendance Program:

Smith, John 1 - change balance to 17.6 hours
Smith, John 2 - change balance to 17.6 hours
Smith, John 3 - change balance to 8.7 hours"

The pop-up would basically give a list for however many entries appear in Column I, if there were 6 names, I'd want it to show 6, and so on and so forth.

This will reset every March and the dates in column I will be cleared to allow for new entries for that year. If there aren't any dates in column I, then no pop-up message is needed.

Note it is possible that some rows can get deleted with terminations of employees above these who are pro-rated. I don't know if that plays into the coding with this but did want to mention.

I hope this makes sense, please let me know if I can clarify anything.

 

[bh24524]

Also of note, the sheet this date and hours balance data appears on is called "Warehouse". Additionally, there is already an event code provided for us prior that triggers a pop-up for another tab upon the workbook open, so this code would be integrated within that one. Separate message pop-up of course. Here is that code just in case:

Private Sub Workbook_Open()
Dim wkStart As Date, wkEnd As Date
Dim d As Long
Dim w As String
Dim Answer As VbMsgBoxResult
    UserForm1.Show vbModeless
wkStart = 1 - Weekday(Date) + Date
wkEnd = DateAdd("d", 6, wkStart)

    With Worksheets("Future Paid Vacations")
        w = "'" & .Name & "'!" & .Range("E3:E" & .Cells(Rows.Count, "E").End(xlUp).Row).Address
    End With

d = Evaluate("SUMPRODUCT((" & w & ">=DATE(" & Year(wkStart) & "," & Month(wkStart) & "," & Day(wkStart) & "))*" & _
                        "(" & w & "<=DATE(" & Year(wkEnd) & "," & Month(wkEnd) & "," & Day(wkEnd) & ")))")
                            
    If d > 0 Then
        Answer = MsgBox(d & IIf(d = 1, " vacation", " vacations") & " entry(s) to be paid from the Future Vacations tab this week." & _
            vbNewLine & vbNewLine & "Do you want to go to Future Paid Vacations tab? ", vbYesNo, "This week: " & Format(wkStart, "ddd mm/dd/yyyy") & " to " & Format(wkEnd, "ddd mm/dd/yyyy"))
        If Answer = vbYes Then
            Worksheets("Future Paid Vacations").Activate
        Else
            Exit Sub
        End If
    End If
End Sub

 

[bh24524]

Currently, I added some conditional formatting that will light up the cells the first 5 days of any month. I don't know if that would make some coding easier to go that approach with giving a message box? Also, I am also okay with the event code being upon clicking a specific tab if altering the code upon the workbook opening to accommodate this is too much of a hassle. Would that make it any easier?