Codes for Reminder creation in Excel

by Francis 2013-06-12 11:20:38

Codes for Reminder creation in Excel
To get Reminder message box automatically, according to the date and time in Excel worksheet.

1. In Worksheet to create date and time.
---A -------------- B -------- C ---------------------- D
1- Date ------- Time ------Task ------------------- Reminder
2- 12/06/2013 - 10:00 ---- Supports -------------- X
3- 12/06/2013 - 12:00 ---- HRS -------------- X
X ==> indicate the this is an active reminder. Remove x to disable that reminder

2. ALT + F11 to press in keyboard
Now to open window, here you can paste below code,

Private Const reminder As Integer = 1
Private reminderNext As Variant

Public Sub remindMe()

currentTime = Time
nextMin = CDate(Format(Time + 1 / (24 * 60), "hh:mm"))

myrows = Range("A1").CurrentRegion.Rows.Count

For thisrow = 2 To myrows
If (Cells(thisrow, "D") = "X") Then

thistime = CDate(CDate(Cells(thisrow, "A")) + Cells(thisrow, "B"))

If ((thistime >= Now) And (thistime <= Now + 1 * reminder / (24 * 60))) Then

task = task & vbCrLf & Cells(thisrow, "C") & " at " & Format(Cells(thisrow, "B"), "hh:mm")

End If
End If


If (task <> "") Then MsgBox task

reminderNext = Now + TimeSerial(0, reminder, 0)
Application.OnTime reminderNext, "ThisWorkbook.remindMe", , True

End Sub

Note :
1. The code needs to be started manually first time. After that till you close the book, it will keep checking the schedule as you have defined in the book

2. Once you close the book and relaunch it, you again have to start the code once more.

3 . To start the code, you would need to go to macro and there is a run button on the menu bar.

4. Now if you don't want to manually run the code, and you want the code to run on its own when ever you start the book, then you have to define a routine called Workbook_Open()

5 So again, If you want to manually run the code then you do exactly as you did before
and if you want to run the code on its own when ever the book is opened, then you need to add one more (you keep the old code though). The code would be

Private Sub Workbook_Open()

Call remindMe

End Sub

You must LOGIN to add comments