How can I trigger a MsgBox on alternate days?
Eg. open a MsgBox beginning on, say, Monday, then cycle through Wed, Fri, Sun, Tue, Thu, Sat, Mon repeat etc.
Or, put another way, start on DateX, then DateX + 2, DateX + 4, DateX + 6 etc.
I'd prefer the first way rather than rely on any specific start date 🙂
Find a cell somewhere in your workbook, enter a 2 (todays weekday number) and give that cell a defined name "NxtMsgDay" (without the quotes).
Then add this code to the ThisWorkbook Object module
Private Sub Workbook_Open()
If Weekday(Date) = [NxtMsgDay] Then
MsgBox "here is the message" 'Change as required
[NxtMsgDay] = [NxtMsgDay] + 2
If [NxtMsgDay] = 8 Then
[NxtMsgDay] = 1
ElseIf [NxtMsgDay] = 9 Then
[NxtMsgDay] = 2
End If
End If
End Sub
Close the workbook, saving it as macro enabled if not already a .xlsm file. Then reopen and you will get the message, the NxtMsgDay cell will increase by 2 and message will be seen again when file gets opened on Wednesday, then again on Friday and so on.
Thank you Charles!
You're welcome.
