Tech Support Forum banner
Status
Not open for further replies.
1 - 11 of 11 Posts

·
Registered
Joined
·
6 Posts
Discussion Starter · #1 ·
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!!!!:4-dontkno,
Iceman 26
 

·
Security Manager, Analyst , Rangemaster, TSF Acade
Joined
·
39,538 Posts
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.
 

·
Registered
Joined
·
6 Posts
Discussion Starter · #4 ·
Thanks Very Much Works Perfectly One More Question

:pray: :pray: :pray: :pray: :pray:​
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
:pray: :pray: :pray: :pray: :pray:​
 

·
Security Manager, Analyst , Rangemaster, TSF Acade
Joined
·
39,538 Posts
You are welcome. :smile:

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.
 

·
Registered
Joined
·
6 Posts
Discussion Starter · #6 ·
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
 

·
Security Manager, Analyst , Rangemaster, TSF Acade
Joined
·
39,538 Posts
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?
 

·
Registered
Joined
·
6 Posts
Discussion Starter · #8 ·
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
:pray: :pray: :pray: :pray: :pray: :pray: :pray:​
 

·
Security Manager, Analyst , Rangemaster, TSF Acade
Joined
·
39,538 Posts
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!
 

·
Security Manager, Analyst , Rangemaster, TSF Acade
Joined
·
39,538 Posts
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.
 
1 - 11 of 11 Posts
Status
Not open for further replies.
Top