Workbook reminder

B

baha17

Hii All,
I tried to make a reminder inside the workbook which works every
minute OnTime method(subject to change of course). Everything works
properly except, if I work with more than one workbook, gives an error
which I understand why. I tried to prevent calling OnTime macro by
activating the workbook that worked fine too. but the problem, the
workbook name which i have reminder changes every day. Is there any
other way to triger
Thanks in advance.
OnTime macro correctly? Here are my codes:

Private Sub Workbook_Open()
Application.OnTime Now + TimeValue("00:01:00"), "Remember"
wbkName = Sheets("Reminder").Range("WbkName")
'Windows(wbkName).Activate
End Sub
Public TrdDate As Date

Sub Remember()
Dim wbkName As Variant
'wbkName = Sheets("Reminder").Range("WbkName")
'Windows(wbkName).Activate
TrdDate = Now + TimeValue("00:01:00")
Application.OnTime TrtDate, "Remember"
CheckReminder
End Sub

Sub CheckReminder()
Dim cel As Range
Dim MyDate As Date
MyDate = Now()
Dim Prompt, Buttons, Title, Help, Ctxt, Response, MyString
Buttons = vbYesNo + vbCritical + vbDefaultButton1
Title = "CALLING FOR REMINDER"
For Each cel In Sheets("Reminder").Range("G:G")
Prompt = "There is a reminder about: " & Chr(13) _
& " { " & cel.Text & " }" & " on " & cel.Offset(0, 1) & " at " &
Format(cel.Offset(0, 2), "HH:MM") _
& Chr(13) _
& "Please Click YES to Dismiss , Click NO to Snooze"
If cel <> "" And cel.Offset(0, 3) = "" _
And Format(cel.Offset(0, 1).Value, "DD-MMM-YY") = Format(MyDate, "DD-
MMM-YY") _
And Format(cel.Offset(0, 2).Value, "HH") = Format(MyDate, "HH") Then
Response = MsgBox(Prompt, Buttons, Title, Help, Ctxt)
If Response = vbYes Then
GoTo 20
Else: GoTo 30
End If
20 cel.Offset(0, 3) = "dismissed"
End If
30 Next cel
End Sub
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top