Solved

Reminder in VBA

Posted on 2014-03-07
8
1,004 Views
Last Modified: 2014-03-10
Hi All,

I'd like to create a reminder using VBA - the concept is fairly straightforward BUT I don't want the reminder to interfer with other macros...

For example, I sit at the PC for a long time each day and I'd like the reminder to tell me to have a stretch every 45 minutes and save my work every 30 minutes.

I was thinking of using Application.OnTime to show a userform and then when the userform is acknowledged it would restart the countdown to the next reminder.

I'm not sure what the traps and pitfalls are to having this kind of code in my project.  If anyone has thoughts / sample code I'd be glad if you could share :-)

PS - needs to work in both Word and Excel - no references to cells, tables etc.

Cheers,
0
Comment
Question by:DrTribos
  • 4
  • 3
8 Comments
 
LVL 81

Assisted Solution

by:byundt
byundt earned 500 total points
ID: 39914250
In Excel, you might put the following code in ThisWorkbook code pane. It is worth noting that only one OnTime procedure may be scheduled at any point in time. If you have two workbooks, only one of them can use OnTime to schedule your reminders.
Dim dStretchTime As Double, dSaveTime As Double

Private Sub Workbook_BeforeClose(Cancel As Boolean)
On Error Resume Next
Application.OnTime IIf(dStretchTime <= dSaveTime, dStretchTime, dSaveTime), "ThisWorkbook.Reminder", , False 'Cancel the OnTime procedure
On Error GoTo 0
End Sub

Private Sub Workbook_Open()
dStretchTime = Now + 45 / 86400
dSaveTime = Now + 30 / 86400
Application.OnTime dSaveTime, "ThisWorkbook.Reminder"    'Initiate the OnTime procedures
End Sub

Private Sub Reminder()
If dStretchTime < dSaveTime Then
    MsgBox "Time to stretch"
    dStretchTime = Now + 45 / 86400
Else
    MsgBox "Time to save your file"
    dSaveTime = Now + 30 / 86400
End If
Application.OnTime IIf(dStretchTime <= dSaveTime, dStretchTime, dSaveTime), "ThisWorkbook.Reminder"
End Sub

Open in new window

Pitfall:
Only one OnTime procedure may be scheduled at any point in time. If you have two workbooks, only one of them can use OnTime to schedule your reminders. If you might ever have two workbooks open at the same time that contain the code to schedule OnTime procedures, it is better to follow a different path. Schedule AutoRecover using the File...Excel Options...Save menu item and specify a path for those file versions.
RemindMeQ28383436.xlsm
0
 
LVL 81

Expert Comment

by:byundt
ID: 39914257
In Word, I think the code could be:
Dim dStretchTime As Double, dSaveTime As Double

Private Sub Document_Open()
dStretchTime = Now + 45 / 86400
dSaveTime = Now + 30 / 86400
Application.OnTime dSaveTime, "Project.ThisDocument.Reminder"    'Initiate the OnTime procedures
End Sub

Private Sub Reminder()
If dStretchTime < dSaveTime Then
    MsgBox "Time to stretch"
    dStretchTime = Now + 45 / 86400
Else
    MsgBox "Time to save your file"
    dSaveTime = Now + 30 / 86400
End If
Application.OnTime IIf(dStretchTime <= dSaveTime, dStretchTime, dSaveTime), "Project.ThisDocument.Reminder"
End Sub

Open in new window

Word's on-line help for the OnTime method suggests putting the code in the ThisDocument code pane of the Normal template. If so, it would apply to all documents to are working with. That's a much more elegant approach than I had suggested for Excel.

In light of the Word documentation I'd like to revise my suggestion for Excel. Put the code in a workbook (such as Personal.xlsb) that is stored in your XLSTART folder. Don't put it in the workbooks you are working on. The macro will still trigger every 30 & 45 minutes, but you won't have any conflicts between workbooks.
0
 
LVL 15

Author Comment

by:DrTribos
ID: 39914260
Thanks - I'll test as soon as I can,  cheers
0
Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

 
LVL 81

Expert Comment

by:byundt
ID: 39914273
I goofed, and made it update in 30 or 45 seconds rather than minutes. Corrected code appears below.

Word code (for ThisDocument in Normal template):
Dim dStretchTime As Double, dSaveTime As Double

Private Sub Document_Open()
dStretchTime = Now + 45 / 1440
dSaveTime = Now + 30 / 1440
Application.OnTime dSaveTime, "ThisDocument.Reminder"    'Initiate the OnTime procedures
End Sub

Private Sub Reminder()
If dStretchTime < dSaveTime Then
    MsgBox "Time to stretch"
    dStretchTime = Now + 45 / 1440
Else
    MsgBox "Time to save your file"
    dSaveTime = Now + 30 / 1440
End If
Application.OnTime IIf(dStretchTime <= dSaveTime, dStretchTime, dSaveTime), "ThisDocument.Reminder"
End Sub

Open in new window


Excel code (for ThisWorkbook code pane in Personal.xlsb, or other workbook in XLSTART folder):
Dim dStretchTime As Double, dSaveTime As Double

Private Sub Workbook_BeforeClose(Cancel As Boolean)
On Error Resume Next
Application.OnTime IIf(dStretchTime <= dSaveTime, dStretchTime, dSaveTime), "ThisWorkbook.Reminder", , False 'Cancel the OnTime procedure
On Error GoTo 0
End Sub

Private Sub Workbook_Open()
dStretchTime = Now + 45 / 1440
dSaveTime = Now + 30 / 1440
Application.OnTime dSaveTime, "ThisWorkbook.Reminder"    'Initiate the OnTime procedures
End Sub

Private Sub Reminder()
If dStretchTime < dSaveTime Then
    MsgBox "Time to stretch"
    dStretchTime = Now + 45 / 1440
Else
    MsgBox "Time to save your file"
    dSaveTime = Now + 30 / 1440
End If
Application.OnTime IIf(dStretchTime <= dSaveTime, dStretchTime, dSaveTime), "ThisWorkbook.Reminder"
End Sub

Open in new window

0
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 39915112
If you use Outlook, you might want to explore setting that up as a recurring task.  No code required :)
0
 
LVL 15

Author Comment

by:DrTribos
ID: 39919202
Sorry for the delay in my reply, work got in the way.

Patrick - It is a fair suggestion however I want this to be specific to the macros that I am writing...

Byundt - Thank you for the code.  Regarding your comment:
In light of the Word documentation I'd like to revise my suggestion for Excel. Put the code in a workbook (such as Personal.xlsb) that is stored in your XLSTART folder. Don't put it in the workbooks you are working on. The macro will still trigger every 30 & 45 minutes, but you won't have any conflicts between workbooks.
Does this effectively mean Application.OnTime can run at the same time in each open book?

In light of this I'm trying to get my head around exactly what I want...
My macros are stored in a template... the template is loaded... On time should be in the "ThisDocument" area (I mainly use MS Word)...  Thinking out loud now... my code WILL NOT be in the STARTUP folder - too messy for my purposes (I think)...  so that seems to leave me with the potential of having several documents open at the one time using the same template, so...

Realistically I would like the save reminder for individual documents after 30 mins of editing BUT the stretch reminder is more like an application thing, and should be fired at fixed intervals on an application level...

Hmmm I think this complicates things a bit!
0
 
LVL 81

Accepted Solution

by:
byundt earned 500 total points
ID: 39919283
Excel only permits one Application.OnTime at a time. If you have multiple workbooks open, then only one of them can be using Application.OnTime. Word has a similar limitation.

For this reason, I suggested putting the Application.OnTime in none of the workbooks you will be using for daily work. Instead, put it in a different workbook that is stored in your XLSTART folder. Such a workbook will automatically open whenever you launch Excel. The Personal.xlsb workbook is stored in the XLSTART folder, and might be a good place for the suggested macros.

If you put the macros in your Personal.xlsb "personal macro workbook" then you will get  a generic message to save your files alternating with a general message to do some stretching. After clicking OK in the MessageBox, you will be on your own to save each of the open workbooks--though a macro could save them all for you automatically. You won't get separate messages for each workbook; instead the sequence will be triggered by the elapsed time since you launched Excel.

Another approach would be to trap workbooks being opened and create/update a named range that contains the alarm time. The Application.OnTime would then loop through all the open workbooks every five minutes and display a personalized save warning at the appropriate time.
0
 
LVL 15

Author Closing Comment

by:DrTribos
ID: 39919291
Yup - I think that is the way forward, great suggestion. Cheers,
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Microsoft Office Picture Manager was included in Office 2003, 2007, and 2010, but not in Office 2013. Users had hopes that it would be in Office 2016/Office 365, but it is not. Fortunately, the same zero-cost technique that works to install it with …
Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question