PDA

View Full Version : Solved: Msg Box on 1st of every month



mykal66
06-20-2012, 11:16 AM
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
06-20-2012, 12:23 PM
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
06-20-2012, 01:39 PM
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
06-20-2012, 02:05 PM
You could try putting this in your ThisWorkbook module:

VBA:

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
06-20-2012, 02:11 PM
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
06-20-2012, 03:26 PM
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
06-20-2012, 03:27 PM
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