Msg Box on 1st of every month

[mykal66]

I need a spreadsheet to automatically pop up a message box on the 1st every month, or first day it is opened after the first of the month.

I tried using = Now() on one cell e.g. A1 then list 1st of month in range e.g. B1:b60 and trying to match it but cant get it to work.

Can anyone help please

[CodeNinja]

Mykal66,
Interesting problem... I would solve it by using the built in document properties for last save time in the workbook_open call. I would save the last time the message was recorded in a cell (can be on a hidden sheet etc) and update that every time the message was given, and on open of the workbook, I would save the workbook.

That looks like this:
Of course you will have to change sheet1.cells(1,1) to whatever sheet and cell you want to save it to...


Private Sub Workbook_Open()
Dim savDt As Date
Dim msgDt As Date
savDt = ThisWorkbook.BuiltinDocumentProperties("Last Save Time")
msgDt = Sheet1.Cells(1, 1)
If Year(msgDt) < Year(savDt) Or Month(msgDt) < Month(savDt) Then
MsgBox ("You have not seen this message yet this month")
Sheet1.Cells(1, 1) = Now
End If
ThisWorkbook.Save
End Sub

[Opv]

I need a spreadsheet to automatically pop up a message box on the 1st every month, or first day it is opened after the first of the month.

I tried using = Now() on one cell e.g. A1 then list 1st of month in range e.g. B1:b60 and trying to match it but cant get it to work.

Can anyone help please

You could try putting this in your ThisWorkbook module:



Private Sub Workbook_Open()
If Day(Date) = 1 Then
MsgBox "Your text here"
End If
End Sub

[CodeNinja]

You could try putting this in your ThisWorkbook module:


Private Sub Workbook_Open()
If Day(Date) = 1 Then
MsgBox "Your text here"
End If
End Sub


That would only work for when the workbook was opened on day 1 of the month... if it was day 2 and the first time opened, it would not trigger...

[Opv]

That would only work for when the workbook was opened on day 1 of the month... if it was day 2 and the first time opened, it would not trigger...

True, I misread the OP.

[mykal66]

Mykal66,
Interesting problem... I would solve it by using the built in document properties for last save time in the workbook_open call. I would save the last time the message was recorded in a cell (can be on a hidden sheet etc) and update that every time the message was given, and on open of the workbook, I would save the workbook.

That looks like this:
Of course you will have to change sheet1.cells(1,1) to whatever sheet and cell you want to save it to...


Private Sub Workbook_Open()
Dim savDt As Date
Dim msgDt As Date
savDt = ThisWorkbook.BuiltinDocumentProperties("Last Save Time")
msgDt = Sheet1.Cells(1, 1)
If Year(msgDt) < Year(savDt) Or Month(msgDt) < Month(savDt) Then
MsgBox ("You have not seen this message yet this month")
Sheet1.Cells(1, 1) = Now
End If
ThisWorkbook.Save
End Sub

Thank you very much, this seems to work fine- Really appreciated

[mykal66]

You could try putting this in your ThisWorkbook module:



Private Sub Workbook_Open()
If Day(Date) = 1 Then
MsgBox "Your text here"
End If
End Sub

Thank you for trying to help anyway