I have two countdown timers running weekly hours and monthly hours. What I need is to sync the timer with the system time to avoid the macro of one timer stopping when a message box pops up when one of the timers reach zero. example: Weekly countdown timer reaches 00:00:00, a message box pops up with what needs to be done. The message box pauses the monthly timer but it activates again when the message box is closed, but the timer starts off on its normal countdown and does not re calculate for the seconds lost while the message box was opened. I have looked all over online and could not find a way around the message box pausing the macros. Is there any way to sync a countdown timer to the system clock so it updates itself after the message box is closed? I have cells "B2"and "B3" formatted as 00:00:00 time with 2 start/reset buttons for each cell.
If I can get help with this I can finally finish this project. Thanks
Code:
ublic Sub StartCountdown1()
StopTimer
CountdownTime = ("7") 'number of days will convert to hours using 00:00:00
UpdateCountdown
End Sub
Public Sub UpdateCountdown()
Worksheets("Pm Scheduler").Range("B2").Value = CountdownTime
CountdownTime = DateAdd("s", -cRunIntervalSeconds, CountdownTime)
If CountdownTime > 0 Then
'Reschedule this procedure
RunWhen = DateAdd("s", cRunIntervalSeconds, Now)
Application.OnTime EarliestTime:=RunWhen, procedure:=cRunWhat, Schedule:=True
Else
'Play sound when countdown reaches zero and reset to 30 seconds
sndPlaySound32 "C:\Windows\Media\ding.wav", &H1
Worksheets("Pm Scheduler").Range("B2").Value = TimeValue("00:00:00")
Application.OnTime Now + TimeValue("00:00:02"), "ShowMsgbox"
End If
End Sub
Public Sub StopTimer()
On Error Resume Next
Application.OnTime EarliestTime:=RunWhen, procedure:=cRunWhat, Schedule:=False
End Sub
Sub showmsgbox()
'Shell ("C:\Program Files\Windows NT\Accessories\wordpad.exe")
'Workbooks.Open Filename:=("C:\Program Files\Windows NT\Accessories\testing this out.rtf")
MsgBox ("1) Check oil level and top off if needed" & vbNewLine & "2) Check for proper functioning of safety switch") & vbNewLine & "3) Clean and lubricate table plate" & vbNewLine & "4) Clean tool holder" & vbNewLine & "5) Drain off condensation from air filter water seperator" & vbNewLine & "6) Clean Attachment rail for gauge finger displacement (Z axis)" & vbNewLine & "7) Clean Guide of support bracket", , "Trumpf 5130 Weekly PM."
If -vbOK Then
Run ("getusername")
End If
End Sub
Sub GetUserName()
Dim strName As String
Dim lr As Long
strName = InputBox("Preventative Maintenence Completed by" & vbNewLine & vbNewLine & vbNewLine & vbNewLine & "Name and Date", "Weekly PM for Trumpf 5130")
If strName = "" Then
Run ("getusername")
Else
lr = Sheet2.Range("A" & Rows.Count).End(xlUp).Row + 1
Sheet2.Range("A" & lr).Value = strName & vbNewLine & Now()
End If
End Sub