Excel - VBA - Creating a reminder

December 2016

Excel is often the tool of choice for manipulating data, generating reports and charts from different types of data. One can, however, also use Excel for doing other interesting things such as creating a reminder. This can be done using VBA framework. To create a reminder a custom VBA code needs to be written which utilises some of the inbuilt functionalities. The code, however, needs to be run manually in the first instance and it would continue to run for next iterations as long as the workbook is open. If the user doesn't want to run the code manually, they have to define a custom routine called workbook_open.


Issue


I want to get a Reminder message box automatically, according to the date and time in an Excel worksheet.

Can this be done in VBA?

Solution


---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 that 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:
  • The code needs to be started manually the first time. After that, until you close the book, it will keep checking the schedule as you have defined in the book.
  • Once you close the book and relaunch it, you again have to start the code once more.
  • To start the code, you would need to go to Macro, and there you'll find a run button on the menu bar.
  • If you don't want to manually run the code, and you want the code to run on its own whenever you start the book, then you have to define a routine called Workbook_Open()
  • So again, If you want to manually run the code then you do exactly as you did before and if you want the code to run on its own whenever 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 

Note


Thanks to rizvisa1 for this tip on the forum.

Related :

This document entitled « Excel - VBA - Creating a reminder » from CCM (ccm.net) is made available under the Creative Commons license. You can copy, modify copies of this page, under the conditions stipulated by the license, as this note appears clearly.