Upon opening the WorkSheet, how to check the date

**alghareeb** · 04-24-2009, 09:01 AM

I have an excel worksheet which I use to update on weekly basis. A date should always be shown on the report and I should update it as of current. sometimes I forgot to change the date. I want the Worksheet every time I open it to check the date whether the date on the report is as of current. If not, I want the worksheet to alert me with msgbox asking me to change the date.

Thanks for help...

**excel_1982** · 04-24-2009, 09:19 AM

On the workbook open or worksheet activate event, you can add code to get current date and time (Now()). Then compare this with the date you have and if the later is older, then display a message box to prompt user to update the date.

**LiamPotter** · 04-24-2009, 09:19 AM

Put the following formula into a cell near the cell containing the date you wish to check:

=TODAY()

Format the cell with that formula so that the format of the date matches the format of the date of the cell containing the date you wish to check. You can also make it white so that this date isn't visible in future, it won't affect the macro.

Put this code into the ThisWorkbook code part of VBA for your workbook:

Private Sub Workbook_Open()

'Change A2 to the cell you put =TODAY() in
'Change A1 to the cell containing the date you want to check

If Range("A2") <> Range("A2") Then
        MsgBox "The date of the workbook does is not today's date! Please change it accordingly.", vbCritical, "Error"
Else
        MsgBox "The date of the workbook is today's date."
End If

End Sub

**dgkindy** · 04-24-2009, 09:20 AM

Put a command in the THISWORKBOOK

PHP Code:


Private Sub Workbook_Open()

        Range("’sheet1’!A1) = Date

End Sub

**LiamPotter** · 04-24-2009, 09:26 AM

Originally Posted by dgkindy

Put a command in the THISWORKBOOK

PHP Code:


Private Sub Workbook_Open()

        Range("’sheet1’!A1) = Date

End Sub

This guy is right - instead of checking when the workbook opens, just make the macro automatically update the date instead.

I'd change the above though to this:

Private Sub Workbook_Open()
'Rename Sheet1 to whatever your sheet is called
        Sheets("Sheet1").Range("A1") = Date
End Sub

**alghareeb** · 04-29-2009, 08:52 AM

Many thanks for all for their help
Unfortunately none of the suggested codes works. The way I wantrf it is that once I open the workbook it should go directly to sheet1 and check the dates and should alert me via msgbox in that respect. Hope that someone helps me.