Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Countdown timer
I would like to create a countdown timer for an excel file to monitor the
time from now an a predetermined time as computed in the worksheet |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Countdown timer
Ira,
Name a cell TimeToGo, and in that cell enter the formula (watch line wrapping) =IF(NOW()<TargetDate,INT(TargetDate-NOW()) & " days, " & TEXT(((TargetDate-NOW()) - INT(TargetDate-NOW())),"hh:mm:ss"),"Done!") Name another cell TargetDate, and in that cell either use a formula to calculate or directly enter the Date/Time that you want to countdown to. Then run StartIt and run StopIt if you want to stop the timer. HTH, Bernie MS Excel MVP Dim NextTime As Date Public Running As Boolean Sub StartIt() Running = True Update End Sub Sub Update() NextTime = Time + TimeValue("00:00:01") If Range("TimeToGo").Value < "Done!" Then Range("TimeToGo").Calculate Application.OnTime NextTime, "Update" End If End Sub Sub StopIt() If Not Running Then Exit Sub On Error Resume Next Application.OnTime NextTime, "Update", schedule:=False Running = False End Sub "Ira" wrote in message ... I would like to create a countdown timer for an excel file to monitor the time from now an a predetermined time as computed in the worksheet |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Countdown timer
Bernie,
The code is realy great! It works like a charm. All i need now is a button to start the timer and a pop up menu to show if the time has expired. I've seen some excel files have buttons to start a macro and others with customized popup screens for alerts and stuff. Thanks! Ira "Bernie Deitrick" wrote: Ira, Name a cell TimeToGo, and in that cell enter the formula (watch line wrapping) =IF(NOW()<TargetDate,INT(TargetDate-NOW()) & " days, " & TEXT(((TargetDate-NOW()) - INT(TargetDate-NOW())),"hh:mm:ss"),"Done!") Name another cell TargetDate, and in that cell either use a formula to calculate or directly enter the Date/Time that you want to countdown to. Then run StartIt and run StopIt if you want to stop the timer. HTH, Bernie MS Excel MVP Dim NextTime As Date Public Running As Boolean Sub StartIt() Running = True Update End Sub Sub Update() NextTime = Time + TimeValue("00:00:01") If Range("TimeToGo").Value < "Done!" Then Range("TimeToGo").Calculate Application.OnTime NextTime, "Update" End If End Sub Sub StopIt() If Not Running Then Exit Sub On Error Resume Next Application.OnTime NextTime, "Update", schedule:=False Running = False End Sub "Ira" wrote in message ... I would like to create a countdown timer for an excel file to monitor the time from now an a predetermined time as computed in the worksheet |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Countdown timer
Ira,
Do you need help with the buttons? Bernie MS Excel MVP "Ira" wrote in message ... Bernie, The code is realy great! It works like a charm. All i need now is a button to start the timer and a pop up menu to show if the time has expired. I've seen some excel files have buttons to start a macro and others with customized popup screens for alerts and stuff. Thanks! Ira "Bernie Deitrick" wrote: Ira, Name a cell TimeToGo, and in that cell enter the formula (watch line wrapping) =IF(NOW()<TargetDate,INT(TargetDate-NOW()) & " days, " & TEXT(((TargetDate-NOW()) - INT(TargetDate-NOW())),"hh:mm:ss"),"Done!") Name another cell TargetDate, and in that cell either use a formula to calculate or directly enter the Date/Time that you want to countdown to. Then run StartIt and run StopIt if you want to stop the timer. HTH, Bernie MS Excel MVP Dim NextTime As Date Public Running As Boolean Sub StartIt() Running = True Update End Sub Sub Update() NextTime = Time + TimeValue("00:00:01") If Range("TimeToGo").Value < "Done!" Then Range("TimeToGo").Calculate Application.OnTime NextTime, "Update" End If End Sub Sub StopIt() If Not Running Then Exit Sub On Error Resume Next Application.OnTime NextTime, "Update", schedule:=False Running = False End Sub "Ira" wrote in message ... I would like to create a countdown timer for an excel file to monitor the time from now an a predetermined time as computed in the worksheet |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Countdown timer
Bernie,
I was able to create customized buttons for a toolbar during my testing to start and stop the macros. However i think it would be better if the buttons existed on the worksheet itself since I would not be the only user of the file. With regards to the popup screens, i would like these to show data from specific cells within worksheet when the timer is done. Thanks for the help! Ira "Bernie Deitrick" wrote: Ira, Do you need help with the buttons? Bernie MS Excel MVP "Ira" wrote in message ... Bernie, The code is realy great! It works like a charm. All i need now is a button to start the timer and a pop up menu to show if the time has expired. I've seen some excel files have buttons to start a macro and others with customized popup screens for alerts and stuff. Thanks! Ira "Bernie Deitrick" wrote: Ira, Name a cell TimeToGo, and in that cell enter the formula (watch line wrapping) =IF(NOW()<TargetDate,INT(TargetDate-NOW()) & " days, " & TEXT(((TargetDate-NOW()) - INT(TargetDate-NOW())),"hh:mm:ss"),"Done!") Name another cell TargetDate, and in that cell either use a formula to calculate or directly enter the Date/Time that you want to countdown to. Then run StartIt and run StopIt if you want to stop the timer. HTH, Bernie MS Excel MVP Dim NextTime As Date Public Running As Boolean Sub StartIt() Running = True Update End Sub Sub Update() NextTime = Time + TimeValue("00:00:01") If Range("TimeToGo").Value < "Done!" Then Range("TimeToGo").Calculate Application.OnTime NextTime, "Update" End If End Sub Sub StopIt() If Not Running Then Exit Sub On Error Resume Next Application.OnTime NextTime, "Update", schedule:=False Running = False End Sub "Ira" wrote in message ... I would like to create a countdown timer for an excel file to monitor the time from now an a predetermined time as computed in the worksheet |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Countdown timer
Use any drawing object and place two on the workbook, and assign the start and stop macros to them
by right-clicking and choosing "Assign macro" As for the pop-up, change If Range("TimeToGo").Value < "Done!" Then Range("TimeToGo").Calculate Application.OnTime NextTime, "Update" End If To soemething along these lines: If Range("TimeToGo").Value < "Done!" Then Range("TimeToGo").Calculate Application.OnTime NextTime, "Update" Else MsgBox "The values that I want to show are" & Chr(10) & _ Range("A1").Value & Chr(10) & _ Range("A2").Value & Chr(10) & _ Range("A3").Value & Chr(10) & _ Range("A4").Value End If HTH, Bernie MS Excel MVP "Ira" wrote in message ... Bernie, I was able to create customized buttons for a toolbar during my testing to start and stop the macros. However i think it would be better if the buttons existed on the worksheet itself since I would not be the only user of the file. With regards to the popup screens, i would like these to show data from specific cells within worksheet when the timer is done. Thanks for the help! Ira "Bernie Deitrick" wrote: Ira, Do you need help with the buttons? Bernie MS Excel MVP "Ira" wrote in message ... Bernie, The code is realy great! It works like a charm. All i need now is a button to start the timer and a pop up menu to show if the time has expired. I've seen some excel files have buttons to start a macro and others with customized popup screens for alerts and stuff. Thanks! Ira "Bernie Deitrick" wrote: Ira, Name a cell TimeToGo, and in that cell enter the formula (watch line wrapping) =IF(NOW()<TargetDate,INT(TargetDate-NOW()) & " days, " & TEXT(((TargetDate-NOW()) - INT(TargetDate-NOW())),"hh:mm:ss"),"Done!") Name another cell TargetDate, and in that cell either use a formula to calculate or directly enter the Date/Time that you want to countdown to. Then run StartIt and run StopIt if you want to stop the timer. HTH, Bernie MS Excel MVP Dim NextTime As Date Public Running As Boolean Sub StartIt() Running = True Update End Sub Sub Update() NextTime = Time + TimeValue("00:00:01") If Range("TimeToGo").Value < "Done!" Then Range("TimeToGo").Calculate Application.OnTime NextTime, "Update" End If End Sub Sub StopIt() If Not Running Then Exit Sub On Error Resume Next Application.OnTime NextTime, "Update", schedule:=False Running = False End Sub "Ira" wrote in message ... I would like to create a countdown timer for an excel file to monitor the time from now an a predetermined time as computed in the worksheet |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Countdown timer
Bernie,
thanks for the help. i was having a bit of difficulty showing the information on the msg box. I'll give it a go and let you know the result. Many thanks! ira "Bernie Deitrick" wrote: Use any drawing object and place two on the workbook, and assign the start and stop macros to them by right-clicking and choosing "Assign macro" As for the pop-up, change If Range("TimeToGo").Value < "Done!" Then Range("TimeToGo").Calculate Application.OnTime NextTime, "Update" End If To soemething along these lines: If Range("TimeToGo").Value < "Done!" Then Range("TimeToGo").Calculate Application.OnTime NextTime, "Update" Else MsgBox "The values that I want to show are" & Chr(10) & _ Range("A1").Value & Chr(10) & _ Range("A2").Value & Chr(10) & _ Range("A3").Value & Chr(10) & _ Range("A4").Value End If HTH, Bernie MS Excel MVP "Ira" wrote in message ... Bernie, I was able to create customized buttons for a toolbar during my testing to start and stop the macros. However i think it would be better if the buttons existed on the worksheet itself since I would not be the only user of the file. With regards to the popup screens, i would like these to show data from specific cells within worksheet when the timer is done. Thanks for the help! Ira "Bernie Deitrick" wrote: Ira, Do you need help with the buttons? Bernie MS Excel MVP "Ira" wrote in message ... Bernie, The code is realy great! It works like a charm. All i need now is a button to start the timer and a pop up menu to show if the time has expired. I've seen some excel files have buttons to start a macro and others with customized popup screens for alerts and stuff. Thanks! Ira "Bernie Deitrick" wrote: Ira, Name a cell TimeToGo, and in that cell enter the formula (watch line wrapping) =IF(NOW()<TargetDate,INT(TargetDate-NOW()) & " days, " & TEXT(((TargetDate-NOW()) - INT(TargetDate-NOW())),"hh:mm:ss"),"Done!") Name another cell TargetDate, and in that cell either use a formula to calculate or directly enter the Date/Time that you want to countdown to. Then run StartIt and run StopIt if you want to stop the timer. HTH, Bernie MS Excel MVP Dim NextTime As Date Public Running As Boolean Sub StartIt() Running = True Update End Sub Sub Update() NextTime = Time + TimeValue("00:00:01") If Range("TimeToGo").Value < "Done!" Then Range("TimeToGo").Calculate Application.OnTime NextTime, "Update" End If End Sub Sub StopIt() If Not Running Then Exit Sub On Error Resume Next Application.OnTime NextTime, "Update", schedule:=False Running = False End Sub "Ira" wrote in message ... I would like to create a countdown timer for an excel file to monitor the time from now an a predetermined time as computed in the worksheet |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Countdown timer
Bernie,
Thanks again for the help! I've been using the timer for some time now and have modifided it even. Best regards, Ira "Ira" wrote: Bernie, thanks for the help. i was having a bit of difficulty showing the information on the msg box. I'll give it a go and let you know the result. Many thanks! ira "Bernie Deitrick" wrote: Use any drawing object and place two on the workbook, and assign the start and stop macros to them by right-clicking and choosing "Assign macro" As for the pop-up, change If Range("TimeToGo").Value < "Done!" Then Range("TimeToGo").Calculate Application.OnTime NextTime, "Update" End If To soemething along these lines: If Range("TimeToGo").Value < "Done!" Then Range("TimeToGo").Calculate Application.OnTime NextTime, "Update" Else MsgBox "The values that I want to show are" & Chr(10) & _ Range("A1").Value & Chr(10) & _ Range("A2").Value & Chr(10) & _ Range("A3").Value & Chr(10) & _ Range("A4").Value End If HTH, Bernie MS Excel MVP "Ira" wrote in message ... Bernie, I was able to create customized buttons for a toolbar during my testing to start and stop the macros. However i think it would be better if the buttons existed on the worksheet itself since I would not be the only user of the file. With regards to the popup screens, i would like these to show data from specific cells within worksheet when the timer is done. Thanks for the help! Ira "Bernie Deitrick" wrote: Ira, Do you need help with the buttons? Bernie MS Excel MVP "Ira" wrote in message ... Bernie, The code is realy great! It works like a charm. All i need now is a button to start the timer and a pop up menu to show if the time has expired. I've seen some excel files have buttons to start a macro and others with customized popup screens for alerts and stuff. Thanks! Ira "Bernie Deitrick" wrote: Ira, Name a cell TimeToGo, and in that cell enter the formula (watch line wrapping) =IF(NOW()<TargetDate,INT(TargetDate-NOW()) & " days, " & TEXT(((TargetDate-NOW()) - INT(TargetDate-NOW())),"hh:mm:ss"),"Done!") Name another cell TargetDate, and in that cell either use a formula to calculate or directly enter the Date/Time that you want to countdown to. Then run StartIt and run StopIt if you want to stop the timer. HTH, Bernie MS Excel MVP Dim NextTime As Date Public Running As Boolean Sub StartIt() Running = True Update End Sub Sub Update() NextTime = Time + TimeValue("00:00:01") If Range("TimeToGo").Value < "Done!" Then Range("TimeToGo").Calculate Application.OnTime NextTime, "Update" End If End Sub Sub StopIt() If Not Running Then Exit Sub On Error Resume Next Application.OnTime NextTime, "Update", schedule:=False Running = False End Sub "Ira" wrote in message ... I would like to create a countdown timer for an excel file to monitor the time from now an a predetermined time as computed in the worksheet |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to create a countdown timer in a worksheet | Excel Worksheet Functions | |||
Countdown Timer | Excel Discussion (Misc queries) | |||
HELP for COUNTDOWN TIMER | Excel Discussion (Misc queries) | |||
Is a countdown timer possible? | Excel Discussion (Misc queries) | |||
Display a countdown timer | Excel Worksheet Functions |