Auto Display a Message Box

[email protected] · 08-03-2006, 11:35 AM

I am using the Today function on several worksheets (same cell address)
in a workbook file. I also have due dates listed in various locations
on all of the worksheets within the workbook.

Is it possible to write code that can compare each due date with the
system date, and if the information is due today or is past due,
display a message box?

I will be most appreciative of any help or direction someone might
offer. This one has me stumped.

Thanks,
Karen

Tom Ogilvy · 08-03-2006, 12:00 PM

are you talking about manually running a macro that will check the dates?

If you just want to mark them, look at Data=>Validation.

--
Regards,
Tom Ogilvy

"[email protected]" wrote:

> I am using the Today function on several worksheets (same cell address)
> in a workbook file. I also have due dates listed in various locations
> on all of the worksheets within the workbook.
>
> Is it possible to write code that can compare each due date with the
> system date, and if the information is due today or is past due,
> display a message box?
>
> I will be most appreciative of any help or direction someone might
> offer. This one has me stumped.
>
> Thanks,
> Karen
>
>

[email protected] · 08-03-2006, 12:30 PM

Thanks for replying Tom. I want the dates checked when the file is
opened, and a message to display if the due dates are >= Current Date.
Is that possible?

Karen

Tom Ogilvy wrote:
> are you talking about manually running a macro that will check the dates?
>
> If you just want to mark them, look at Data=>Validation.
>
> --
> Regards,
> Tom Ogilvy
>
>
> "[email protected]" wrote:
>
> > I am using the Today function on several worksheets (same cell address)
> > in a workbook file. I also have due dates listed in various locations
> > on all of the worksheets within the workbook.
> >
> > Is it possible to write code that can compare each due date with the
> > system date, and if the information is due today or is past due,
> > display a message box?
> >
> > I will be most appreciative of any help or direction someone might
> > offer. This one has me stumped.
> >
> > Thanks,
> > Karen
> >
> >

**bhofsetz** · 08-03-2006, 12:48 PM

Karen,
In the ThisWorkbook module you can use a Workbook_Open event handler.
When the workbook opens it will run any code you have there. Put your code to loop through all the worksheets on in ThisWorkbook (use a For Each Worksehet in ThisWorkbook loop) and check your due date against 'Date'. The example assumes your due date is in Range A1. If your due date ends up in a different cell on each worksheet then this will complicate things.

Private Sub Workbook_Open()
Dim wks As Object
    For Each wks In ThisWorkbook.Worksheets
    If wks.Range("A1") = Date Then
        wks.Activate
        wks.Range("A1").Select
        MsgBox "This project is due today!"
    ElseIf wks.Range("A1") < Date Then
        wks.Activate
        wks.Range("A1").Select
        MsgBox "This project is OVERDUE!"
    End If
    Next wks
End Sub

HTH

[email protected] · 08-03-2006, 01:25 PM

I think this will do the job! The fact that you included the sample
code was the BEST! Thank you so much for your assistance.

bhofsetz wrote:
> Karen,
> In the ThisWorkbook module you can use a Workbook_Open event
> handler.
> When the workbook opens it will run any code you have there. Put your
> code to loop through all the worksheets on in ThisWorkbook (use a For
> Each Worksehet in ThisWorkbook loop) and check your due date against
> 'Date'. The example assumes your due date is in Range A1. If your due
> date ends up in a different cell on each worksheet then this will
> complicate things.
>
>
> Code:
> --------------------
> Private Sub Workbook_Open()
> Dim wks As Object
> For Each wks In ThisWorkbook.Worksheets
> If wks.Range("A1") <= Date Then
> wks.Activate
> wks.Range("A1").Select
> MsgBox "This project is overdue!"
> End If
> Next wks
> End Sub
>
> --------------------
>
>
> HTH
>
>
> --
> bhofsetz
> ------------------------------------------------------------------------
> bhofsetz's Profile: http://www.excelforum.com/member.php...o&userid=18807
> View this thread: http://www.excelforum.com/showthread...hreadid=567944