Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Reminder in VBA

Posted on 2014-03-07
8
Medium Priority
?
1,095 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
8 Comments
 
LVL 81

Assisted Solution

by:byundt
byundt earned 2000 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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
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 93

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 2000 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

Office 365 Training for IT Pros

Learn how to provision tenants, synchronize on-premise Active Directory, implement Single Sign-On, customize Office deployment, and protect your organization with eDiscovery and DLP policies.  Only from Platform Scholar.

Question has a verified solution.

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

Ever visit a website where you spotted a really cool looking Font, yet couldn't figure out which font family it belonged to, or how to get a copy of it for your own use? This article explains the process of doing exactly that, as well as showing how…
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

730 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