I have a spreadsheet that requires the user to enter specific information every hour on the hour. I am looking for assistance on a macro that will always run when the file is open that will have a "pop-up" box appear every hour on the hour (based on PC's time) as a reminder and will require the user to acknowledge the box for it to go away.
-
If you would like to post, please check out the MrExcel Message Board FAQ and register here. If you forgot your password, you can reset your password.
Time Reminder "Pop-up" Macro
- Thread starter Hootywho
- Start date
VoG
Legend
- Joined
- Jun 19, 2002
- Messages
- 63,650
Take a look at the OnTime method http://www.ozgrid.com/Excel/run-macro-on-time.htm
Upvote
0
Thanks Peter, I believe that is a start for what I am trying to do. Unfortunately I am not as proficient with writing code as others. How would I write code to have a box pop-up with specific verbiage that would require a click of some sort to make it disappear?<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com
fficeffice" /><o
></o>
fficeffice" /><o></o>
Upvote
0
Thanks Peter. I have played around with what I took from the link you sent in conjunction with what you provided. Unfortunately I have not been able to get what I want in adjusting times, etc. When we open the workbook is always different so the "every (1 hr)" does not work. I need the message box to pop every hour on the hour no matter what time the workbook was opened.<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
Thanks<o></o>
<o> </o>
fficeffice" /><o></o><o
></o>Thanks<o
></o><o
> </o>
Upvote
0
I did find what seemed to work for me in an earlier post. Unfortunately I do not understand how and cannot troubleshoot the code. My message box pops up on PM but does not once time rolls over to AM. Original question was to have message box pop up every hour on the hour. I have the following in the workbook VBA..
...and the following in a module...
Any help would be appreciated.
Code:
Private Sub Workbook_Open()
MsgBox "Don't Forget To Do A Save As And Change Your File Name. NEVER Work The MASTER File."
StartTimer
End Sub...and the following in a module...
Code:
Public RunWhen As Double
Public Const cRunWhat = "Display_Message" ' the name of the procedure to run
Sub StartTimer()
Select Case Time
Case Is < TimeValue("00:59"): RunWhen = TimeValue("00:59")
Case Is < TimeValue("01:59"): RunWhen = TimeValue("01:59")
Case Is < TimeValue("02:59"): RunWhen = TimeValue("02:59")
Case Is < TimeValue("03:59"): RunWhen = TimeValue("03:59")
Case Is < TimeValue("04:59"): RunWhen = TimeValue("04:59")
Case Is < TimeValue("05:59"): RunWhen = TimeValue("05:59")
Case Is < TimeValue("06:59"): RunWhen = TimeValue("06:59")
Case Is < TimeValue("07:59"): RunWhen = TimeValue("07:59")
Case Is < TimeValue("08:59"): RunWhen = TimeValue("08:59")
Case Is < TimeValue("09:59"): RunWhen = TimeValue("09:59")
Case Is < TimeValue("10:59"): RunWhen = TimeValue("10:59")
Case Is < TimeValue("11:59"): RunWhen = TimeValue("11:59")
Case Is < TimeValue("12:59"): RunWhen = TimeValue("12:59")
Case Is < TimeValue("13:59"): RunWhen = TimeValue("13:59")
Case Is < TimeValue("14:59"): RunWhen = TimeValue("14:59")
Case Is < TimeValue("15:59"): RunWhen = TimeValue("15:59")
Case Is < TimeValue("16:59"): RunWhen = TimeValue("16:59")
Case Is < TimeValue("17:59"): RunWhen = TimeValue("17:59")
Case Is < TimeValue("18:59"): RunWhen = TimeValue("18:59")
Case Is < TimeValue("19:59"): RunWhen = TimeValue("19:59")
Case Is < TimeValue("20:59"): RunWhen = TimeValue("20:59")
Case Is < TimeValue("21:59"): RunWhen = TimeValue("21:59")
Case Is < TimeValue("22:59"): RunWhen = TimeValue("22:59")
Case Is < TimeValue("23:59"): RunWhen = TimeValue("23:59")
'Case Else: RunWhen = TimeValue("11:59")
End Select
Application.OnTime EarliestTime:=RunWhen, Procedure:=cRunWhat, _
Schedule:=True
End Sub
Sub Display_Message()
MsgBox "Complete Your Hourly Stats Now, Headcount, Net Sales, Total Games"
StartTimer
End SubAny help would be appreciated.
Upvote
0
I have searched and searched this site as well as "Googled" what I am trying to accomplish here and have found nothing that works. I have studied the site VoG recommended and unless I am missing something all code there works on an amount of time “after” the file has been opened. My goal is a macro that will display a message box every hour on the hour (based on the PC’s clock) no matter when the file was open. What I have included (code) here seems to work periodically but seems to be sporadic and has issues after midnight. <?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o> </o>
Is there anyone that has seen code for every hour on the hour that works? Thanks so much, I do appreciate your response!<o></o>
fficeffice" /><o></o><o
> </o>Is there anyone that has seen code for every hour on the hour that works? Thanks so much, I do appreciate your response!<o
></o>
Upvote
0
VoG
Legend
- Joined
- Jun 19, 2002
- Messages
- 63,650
I think that what you need is a way of getting the next time on the hour. See if this helps
You could calculate t like that and use Date+t as the time argument with the OnTime method.
Code:
Sub test()
Dim t As Date
t = TimeSerial(Hour(Time) + 1, 0, 0)
MsgBox Format(t, "hh:mm:ss")
End Sub
Upvote
0
Thanks VoG, I will try anything at this point. Will you please clarify as to what would go where please sir (worksheet / module). I'm assuming I am to scratch what I have shown above and go with a midfied version of the Ozgrid code.
Upvote
0
VoG
Legend
- Joined
- Jun 19, 2002
- Messages
- 63,650
In the ThisWorkbook module
In any Standard Module (Insert>Module) place this;
Code:
Private Sub Workbook_Open()
Dim t As Date
t = Date + TimeSerial(Hour(Time) + 1, 0, 0)
Application.OnTime t, "MyMacro"
End Sub
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.OnTime dTime, "MyMacro", , False
End SubIn any Standard Module (Insert>Module) place this;
Code:
Public dTime As Date
Sub MyMacro()
dTime = Now + TimeSerial(1, 0, 0)
Application.OnTime dTime, "MyMacro"
'
'YOUR CODE
'
End Sub
Upvote
0
Similar threads
- Question
