Solved

Reminder in VBA

Posted on 2014-03-07
8
938 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 80

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 80

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 14

Author Comment

by:DrTribos
ID: 39914260
Thanks - I'll test as soon as I can,  cheers
0
 
LVL 80

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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
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 14

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 80

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 14

Author Closing Comment

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

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

PaperPort has a feature called the "Send To Bar". It provides a convenient, drag-and-drop interface for using other installed software, such as Microsoft Office. However, this article shows that the latest Office 2016 apps (installed with an Office …
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 longer labels with horizontal bar charts instead of the vertical column chart.
Office 365 is currently available in five editions. Three of them are for business use: Office 365 Business Essentials, Office 365 Business, and Office 365 Business Premium. Two of them are for home/personal use: Office 365 Home and Office 365 Perso…

760 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now