Codes for Reminder creation in Excel VBA [Solved/Closed]

koyel - Mar 13, 2010 at 02:29 AM - Latest reply:  vivek
- May 17, 2011 at 11:20 PM
Hello,

I want to get Reminder message box automatically, according to the date and time in Excel worksheet.
What are the VBA codings & procedures? Pls help...
See more 

23 replies

Best answer
rizvisa1 4481 Posts Thursday January 28, 2010Registration dateContributorStatus January 6, 2016 Last seen - Mar 13, 2010 at 11:04 AM
53
Thank you
Design your sheet as

---A -------------- B -------- C ---------------------- D
1- Date ------- Time ------Task ------------------- Remind
2- 13/3/2010 - 11:10 ---- Clean Room -------------- X
3- 13/3/2010 - 11:10 ---- Clean Car ----------------- X

X: indicate the this is an active reminder. Remove x to disable that reminder

On VBE (ALT + F11) and in the project explorer window, double click on "ThisWorkbook". Paste the code below.

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 
     
    Next 
     
    If (task <> "") Then MsgBox task 
     
     
    reminderNext = Now + TimeSerial(0, reminder, 0) 
    Application.OnTime reminderNext, "ThisWorkbook.remindMe", , True 
     
End Sub 





Also you need to understand a few things
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

Thank you, rizvisa1 53

Something to say? Add comment

CCM has helped 1676 users this month

rizvisa1 4481 Posts Thursday January 28, 2010Registration dateContributorStatus January 6, 2016 Last seen - May 30, 2010 at 01:47 PM
Not by excel alone. What you need to do is set up a schedule that would open your excel at a given schedule. Click on help of WINDOWS, again help of WINDOWS and NOT excel. and search for schedule task.
Thanks for the code. Is there any way to have Excel play a sound when the message box pops up?
rizvisa1 4481 Posts Thursday January 28, 2010Registration dateContributorStatus January 6, 2016 Last seen - Jul 13, 2010 at 07:00 AM
can anyone tell where to write this code in excel.????
Nagesh
First open excel then enter Alt+F11, go to insert, open module then write these code