VB Code Help Set up Hourly Event

Iceman26 · 12-10-2006, 08:55 PM

I would like some help with setting up a message box to come up every day every hour on the hour, it needs to be a reminder for a worker to do a check, it needs to happen exactly on the hour eg. 12am 1am 2am etc. There are two workers who do this in rotating shifts over the twenty four hour period.Is there anyone out there who could help me please!!!!

,
Iceman 26

**Glaswegian** · 12-11-2006, 02:33 AM

Hi and welcome to TSF.

This should do what you want. This first part goes in ThisWorkbook Module.

Code:

Private Sub Workbook_Open()
StartTheClock
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
StopTheClock
End Sub

And the next bit goes in a normal Module.

Code:

Public ScheduleTime As Double


Sub StartTheClock()
Dim nextHour As Integer

nextHour = Hour(Time) + 1
ScheduleTime = TimeValue(nextHour & ":00:00")
Application.OnTime ScheduleTime, "PopMessage"
End Sub

Sub PopMessage()
MsgBox "Wake up! - It's time to do that check!"
End Sub

Sub StopTheClock()
On Error Resume Next
Application.OnTime EarliestTime:=ScheduleTime, Procedure:="StartTheClock", schedule:=False
End Sub

Remember to change the message to one that suits you.

Post back if you have any problems.

Iceman26 · 12-11-2006, 12:40 PM

I will try that thank you very much Glaswegian, Iceman 26

Iceman26 · 12-11-2006, 02:39 PM

Thanks very much Glaswegian I have one more question you may be able to help me with, In that same sheet I would like to set up a reminder that will come up on the half hour if the log has not been done.
Thanks very much again Iceman26

**Glaswegian** · 12-11-2006, 03:39 PM

You are welcome.

Can you give me a bit more info about this other reminder. Is this separate from the hourly message? Is it, in effect, changing the hourly message to a half hourly message or do you mean something different? Is the new reminder based on an entry in a sheet?

The more detail you give me, the easier it will be for me to provide you with a solution.

Iceman26 · 12-11-2006, 04:19 PM

This reminder is based on an entry in the the sheet, the sheet has a drop down arrow box that is in regards to the hourly check that has the choice of heard or error, i need the message box to come up on the half hour only if these have not been filled, Thank you again for your patience and your help
it is very much appreciated, Iceman26

**Glaswegian** · 12-12-2006, 03:03 PM

OK, so you need a message to appear every half hour if the dropdown box has not been filled - yes? What are the possible values for the dropdown box? Does it normally show a blank and then a value after completion? What is the cell address of the dropdown and sheet name?

Iceman26 · 12-12-2006, 03:31 PM

Yes to the first question, The possible variables are "Heard" or "Error", it normally shows blank then a value after completion, the cell address of the dropdown and sheet name are "C:6 to G:29" inclusive "Sheet1", The cells C:6 & G:6 represent the check for 01:00:00 UTC, C:7 & G:7 for 02:00:00 UTC etc.
& is there a way of making excel maximise when the message box comes up!!!
I am very very grateful for your help and your time, Thanks Iceman26

**Glaswegian** · 12-12-2006, 04:27 PM

Hi Iceman26

The easy bit - to maximise Excel, add this line just before your message box line

Code:

Application.WindowState = xlMaximized

I'll have a look at the other question tomorrow - it's a bit late here now and I really need to get some sleep!

**Glaswegian** · 12-13-2006, 01:14 PM

Hi again

Try this:

These first declarations go at the top of the Module, i.e, before any subs

Code:

Public RunWhen As Double
Public Const cRunIntervalMins = 30    ' thirty minutes
Public Const cRunWhat = "HalfHourWarning"

Then the next 2 routines go in any normal Module

Code:

Sub StartTimer()

RunWhen = Now + TimeSerial(0, cRunIntervalMins, 0)
Application.OnTime earliesttime:=RunWhen, procedure:=cRunWhat, _
     schedule:=True
End Sub


Sub HalfHourWarning()
If Sheets("Sheet1").Range("B1").Value = "" Then
    MsgBox "Don't forget to check it...", vbInformation, "Check it..."
End If
StartTimer
End Sub

This simply an adaptation of Chip Pearson's method for running procedures after a specific interval. The first sub runs the second sub, and the last line in the second, 'StartTimer' resets the first sub to run again, and so on.

Hope this works for you.

Iceman26 · 12-13-2006, 01:54 PM

Thanks Very Much I will see how it goes!!!!

12-10-2006, 08:55 PM	#1 (permalink)
Iceman26 Registered User Join Date: Dec 2006 Location: Braidwood NSW Australia Posts: 6 OS: 2000	VB Code Help Set up Hourly Event I would like some help with setting up a message box to come up every day every hour on the hour, it needs to be a reminder for a worker to do a check, it needs to happen exactly on the hour eg. 12am 1am 2am etc. There are two workers who do this in rotating shifts over the twenty four hour period.Is there anyone out there who could help me please!!!!, Iceman 26

12-11-2006, 02:33 AM	#2 (permalink)
Glaswegian Moderator/ Rangemaster TSF Academy; Analyst, Security Team; Oor Wullie; TSF Surgeon and Resident Comic Join Date: Sep 2005 Location: Glasgow Posts: 25,470 OS: Win XP Pro SP3 / Win 7 Pro My System Blog Entries: 10	Hi and welcome to TSF. This should do what you want. This first part goes in ThisWorkbook Module. Code: Private Sub Workbook_Open() StartTheClock End Sub Private Sub Workbook_BeforeClose(Cancel As Boolean) StopTheClock End Sub And the next bit goes in a normal Module. Code: Public ScheduleTime As Double Sub StartTheClock() Dim nextHour As Integer nextHour = Hour(Time) + 1 ScheduleTime = TimeValue(nextHour & ":00:00") Application.OnTime ScheduleTime, "PopMessage" End Sub Sub PopMessage() MsgBox "Wake up! - It's time to do that check!" End Sub Sub StopTheClock() On Error Resume Next Application.OnTime EarliestTime:=ScheduleTime, Procedure:="StartTheClock", schedule:=False End Sub Remember to change the message to one that suits you. Post back if you have any problems. __________________ Iain - Defender of the Haggis and all things Scottish. I don't help by PM - post in the Forums. PC Safety & Security::PC running a bit slow?::Donate::Photographers Corner Last edited by Glaswegian; 12-11-2006 at 02:38 AM.

12-11-2006, 12:40 PM	#3 (permalink)
Iceman26 Registered User Join Date: Dec 2006 Location: Braidwood NSW Australia Posts: 6 OS: 2000	VB Code Help Set up Hourly Event I will try that thank you very much Glaswegian, Iceman 26

12-11-2006, 02:39 PM	#4 (permalink)
Iceman26 Registered User Join Date: Dec 2006 Location: Braidwood NSW Australia Posts: 6 OS: 2000	Thanks Very Much Works Perfectly One More Question Thanks very much Glaswegian I have one more question you may be able to help me with, In that same sheet I would like to set up a reminder that will come up on the half hour if the log has not been done. Thanks very much again Iceman26

12-11-2006, 03:39 PM	#5 (permalink)
Glaswegian Moderator/ Rangemaster TSF Academy; Analyst, Security Team; Oor Wullie; TSF Surgeon and Resident Comic Join Date: Sep 2005 Location: Glasgow Posts: 25,470 OS: Win XP Pro SP3 / Win 7 Pro My System Blog Entries: 10	You are welcome. Can you give me a bit more info about this other reminder. Is this separate from the hourly message? Is it, in effect, changing the hourly message to a half hourly message or do you mean something different? Is the new reminder based on an entry in a sheet? The more detail you give me, the easier it will be for me to provide you with a solution. __________________ Iain - Defender of the Haggis and all things Scottish. I don't help by PM - post in the Forums. PC Safety & Security::PC running a bit slow?::Donate::Photographers Corner

12-11-2006, 04:19 PM	#6 (permalink)
Iceman26 Registered User Join Date: Dec 2006 Location: Braidwood NSW Australia Posts: 6 OS: 2000	New Reminder This reminder is based on an entry in the the sheet, the sheet has a drop down arrow box that is in regards to the hourly check that has the choice of heard or error, i need the message box to come up on the half hour only if these have not been filled, Thank you again for your patience and your help it is very much appreciated, Iceman26

12-12-2006, 03:03 PM	#7 (permalink)
Glaswegian Moderator/ Rangemaster TSF Academy; Analyst, Security Team; Oor Wullie; TSF Surgeon and Resident Comic Join Date: Sep 2005 Location: Glasgow Posts: 25,470 OS: Win XP Pro SP3 / Win 7 Pro My System Blog Entries: 10	OK, so you need a message to appear every half hour if the dropdown box has not been filled - yes? What are the possible values for the dropdown box? Does it normally show a blank and then a value after completion? What is the cell address of the dropdown and sheet name? __________________ Iain - Defender of the Haggis and all things Scottish. I don't help by PM - post in the Forums. PC Safety & Security::PC running a bit slow?::Donate::Photographers Corner

12-12-2006, 03:31 PM	#8 (permalink)
Iceman26 Registered User Join Date: Dec 2006 Location: Braidwood NSW Australia Posts: 6 OS: 2000	New Reminder Yes to the first question, The possible variables are "Heard" or "Error", it normally shows blank then a value after completion, the cell address of the dropdown and sheet name are "C:6 to G:29" inclusive "Sheet1", The cells C:6 & G:6 represent the check for 01:00:00 UTC, C:7 & G:7 for 02:00:00 UTC etc. & is there a way of making excel maximise when the message box comes up!!! I am very very grateful for your help and your time, Thanks Iceman26

12-12-2006, 04:27 PM	#9 (permalink)
Glaswegian Moderator/ Rangemaster TSF Academy; Analyst, Security Team; Oor Wullie; TSF Surgeon and Resident Comic Join Date: Sep 2005 Location: Glasgow Posts: 25,470 OS: Win XP Pro SP3 / Win 7 Pro My System Blog Entries: 10	Hi Iceman26 The easy bit - to maximise Excel, add this line just before your message box line Code: Application.WindowState = xlMaximized I'll have a look at the other question tomorrow - it's a bit late here now and I really need to get some sleep! __________________ Iain - Defender of the Haggis and all things Scottish. I don't help by PM - post in the Forums. PC Safety & Security::PC running a bit slow?::Donate::Photographers Corner

12-13-2006, 01:14 PM	#10 (permalink)
Glaswegian Moderator/ Rangemaster TSF Academy; Analyst, Security Team; Oor Wullie; TSF Surgeon and Resident Comic Join Date: Sep 2005 Location: Glasgow Posts: 25,470 OS: Win XP Pro SP3 / Win 7 Pro My System Blog Entries: 10	Hi again Try this: These first declarations go at the top of the Module, i.e, before any subs Code: Public RunWhen As Double Public Const cRunIntervalMins = 30 ' thirty minutes Public Const cRunWhat = "HalfHourWarning" Then the next 2 routines go in any normal Module Code: Sub StartTimer() RunWhen = Now + TimeSerial(0, cRunIntervalMins, 0) Application.OnTime earliesttime:=RunWhen, procedure:=cRunWhat, _ schedule:=True End Sub Sub HalfHourWarning() If Sheets("Sheet1").Range("B1").Value = "" Then MsgBox "Don't forget to check it...", vbInformation, "Check it..." End If StartTimer End Sub This simply an adaptation of Chip Pearson's method for running procedures after a specific interval. The first sub runs the second sub, and the last line in the second, 'StartTimer' resets the first sub to run again, and so on. Hope this works for you. __________________ Iain - Defender of the Haggis and all things Scottish. I don't help by PM - post in the Forums. PC Safety & Security::PC running a bit slow?::Donate::Photographers Corner

12-13-2006, 01:54 PM	#11 (permalink)
Iceman26 Registered User Join Date: Dec 2006 Location: Braidwood NSW Australia Posts: 6 OS: 2000	Thanks Very Much I will see how it goes!!!!