Can someone please make a macro that can be a remainder??? Thanks in advance!

[pedie]

If the time entered in Is it business or personal? & date in b2 matches, then I want a message box to pop up with the word entered on b3. If this is possible then it's gonna be great!!! Please please take a look and help me. Thanks again.

<TABLE style="WIDTH: 560pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=745 border=0><COLGROUP><COL style="WIDTH: 58pt; mso-width-source: userset; mso-width-alt: 2816" width=77><COL style="WIDTH: 115pt; mso-width-source: userset; mso-width-alt: 5595" width=153><COL style="WIDTH: 85pt; mso-width-source: userset; mso-width-alt: 4132" width=113><COL style="WIDTH: 145pt; mso-width-source: userset; mso-width-alt: 7058" width=193><COL style="WIDTH: 109pt; mso-width-source: userset; mso-width-alt: 5302" width=145><COL style="WIDTH: 48pt" width=64><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 58pt; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=77 height=17></TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 115pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=153></TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 85pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=113></TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 145pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=193></TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 109pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=145></TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 48pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=64></TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl70 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: #948b54" height=17> </TD><TD class=xl70 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #948b54">A</TD><TD class=xl70 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #948b54">B</TD><TD class=xl70 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #948b54">C</TD><TD class=xl70 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #948b54">D</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"></TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl70 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: #948b54" height=17>1</TD><TD class=xl71 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #b6dde8">time</TD><TD class=xl72 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #b6dde8">1:40 AM</TD><TD class=xl71 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #b6dde8"></TD><TD class=xl74 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #b6dde8"> </TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"></TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl70 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: #948b54" height=17>2</TD><TD class=xl71 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #b6dde8">date</TD><TD class=xl73 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #b6dde8">7/14/2010</TD><TD class=xl71 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #b6dde8"></TD><TD class=xl74 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #b6dde8"> </TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"></TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl70 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: #948b54" height=17>3</TD><TD class=xl71 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #b6dde8">Remainder comments</TD><TD class=xl71 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #b6dde8">Log off</TD><TD class=xl71 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #b6dde8"> </TD><TD class=xl74 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #b6dde8"> </TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"></TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl70 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: #948b54" height=17> </TD><TD class=xl74 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #b6dde8"> </TD><TD class=xl74 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #b6dde8"> </TD><TD class=xl74 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #b6dde8"> </TD><TD class=xl74 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #b6dde8"> </TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"></TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17></TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"></TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"></TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"></TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"></TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"></TD></TR></TBODY></TABLE>

 

[pedie]

Do I have to enter time in the macro as below?
Sub Pedie()
Static done As Boolean
If Not done Then
If Range("B1").Value >= "1:52:00 AM" And Range("B2").Value >= "7/17/2010" Then
done = True
MsgBox Range("B3").Value
End If
End If
End Sub

 

[pedie]

Do I have to enter time in the macro as below?
Sub Pedie()
Static done As Boolean
If Not done Then
If Range("B1").Value >= "2:07:00 AM" And Range("B2").Value >= "7/17/2010" Then
done = True
MsgBox Range("B3").Value
End If
End If
End Sub
Even if I do it this way it does not work...i think i'm doing something wrong Vog!

 

[pedie]

i think something is wrong with my question as well:
What I actually want here is:
For instance, I have 2:25:00 AM entered in B1 of Sheet1
7/14/2010 entered in B2 of sheet1
when the standard time that is the normal clock that works in excel; =now(). matches the time entered in B1 and date in b2 respectively
I want the macro to be trigered and message pop up....with the comments entered in B3.
Thanks again everyone!!!

 

[mikerickson]

Are you going to have a sequence of these or will there be only one date/time/message for the worksheet.

A combination of Workbook_Open & Worksheet_Change events could work with Application.OnTime. But it would be very fragile.

I'm not familiar with Windows, but I'm fairly sure that it comes with standard applications better suited for this kind of Alarm Clock than Excel.

 

[pedie]

May I ask, how can i know what is the time in my macro?

I am sure how should i go and enter things here. I just put the macro you prepapred in my sheet & it is not happening.

I thought when the time and date entered in cell B1 and B1 match current system time and date it would triger the macro and
remain me with the pop up....

 

[mikerickson]

Rather than the OnTime method, perhaps putting this in the ThisWorkbook code module will work.

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Excel.Range)
    On Error GoTo Halt
    With ThisWorkbook.Sheets("Sheet1")
        If IsDate(.Range("B1").Value) And IsDate(.Range("B2").Value) Then
            If CDate(.Range("B1").Value) + CDate(.Range("B2").Value) <= Now Then
            Application.EnableEvents = False
                MsgBox CStr(.Range("B3").Value)
            .Range("B1").Value = "Displayed on " & .Range("B1").Value
            End If
        End If
    End With
Halt:
    Application.EnableEvents = True
End Sub

 

[pedie]

Do you really look like that in real mikerickson? just curious....thank you so much for the code..

I'm going to try now..


Thanks alot...thank you.

 

[pedie]

i entered time in when i want the macro to run in cell B1
and date on B3
and the comment that i want to see when the macro run in B3 and waited;

when the time striked the time entered in b1 it did not run the macro; I just dont know why.

suppose i have entered 9:30:00 PM in B1 and 7/14/2010 as date in b2 and waited for the time to reach the time value that was entered in b1.

it did not run the macro.

Please help me do it right...

please help. thanks

 

[mikerickson]

I think this will do what you want. The range in the function RangeOfAlarm should be adjusted to your situation.

in a normal module

Option Explicit

Public lastTime As Date

Sub SetTimer()
    Dim timeInput As Date, dateInput As Date, timeGiven As Date
    With RangeOfAlarm()
        On Error GoTo Halt1
        dateInput = CDate(.Cells(1, 1).Value)
        timeInput = CDate(Evaluate("mod(" & CDbl(CDate(.Cells(2, 1).Value)) & ", 1)"))
        timeGiven = dateInput + timeInput
Halt1:
        On Error GoTo Halt2

        If lastTime <> timeGiven Then

            Rem clear alarm
            On Error Resume Next
                Application.OnTime Earliesttime:=lastTime, Procedure:="ShowReminder", Schedule:=False
                .Interior.ColorIndex = 48: Rem med grey indicates off
                lastTime = 0
            On Error GoTo Halt2
            
            If Now < timeGiven Then
                Rem set alarm
                Application.OnTime Earliesttime:=timeGiven, Procedure:="ShowReminder"
                .Interior.ColorIndex = 38: Rem rose indicates timer on
                lastTime = timeGiven
            End If
            
        End If
    End With
Halt2:
On Error GoTo 0
End Sub

Sub ShowReminder()
    On Error Resume Next
    With RangeOfAlarm()
        MsgBox Prompt:=CStr(.Cells(3, 1).Value), Title:="Alarm"
        .Interior.ColorIndex = 48: Rem med grey indicates off
        lastTime = 0
    End With
    On Error GoTo 0
End Sub

Function RangeOfAlarm() As Range
    On Error Resume Next
    Set RangeOfAlarm = ThisWorkbook.Sheets("Sheet1").Range("B1:B3"): Rem adjust
    On Error GoTo 0
End Function

and this in the ThisWorkbook code module

Private Sub Workbook_Open()
    On Error Resume Next
    Call SetTimer
    On Error GoTo 0
End Sub

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Excel.Range)
    Dim keyRange As Range
    On Error GoTo Halt
    With RangeOfAlarm()
        Set keyRange = .Cells
        On Error Resume Next
        Set keyRange = Application.Union(keyRange, .Precedents)
        On Error GoTo Halt
    End With
    If Not Application.Intersect(Target, keyRange) Is Nothing Then
        Call SetTimer
    End If
Halt:
On Error GoTo 0
End Sub

If the date and time in B1:B2 is after NOW, the alarm will be set to go off at that time. Otherwise the alarm will be turned off.
When the alarm goes off, a MsgBox will display the contents of B3.
The cells of B1:B3 will change color to show whether the alarm is set.