Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Timer to run when moving to another sheet

0
  1. Hi, I've watched a video about how to make a timer and it's very very helpful, thank you for this. However I need the timer to continue counting when I move to another sheets in the same file. Now it stops. I would be very grateful for a response, I am VBA begginer and need the timer for the file used at work.

Thank you on advance,

Natalia (Poland) :)

Answer
Discuss

Discussion

Hi Natalia and welcome to the Forum  

Which of Don's video tutorial do you mean please?  (Title?) 

You may be interested in an Answer I gave recently , here:  How to make a day timer
John_Ru (rep: 6102) Nov 18, '21 at 4:58 pm
Hi John, thank you very much for a quick response. I've watched 'Countdown timer on Excel" video.
I've also read the discussion that you linked, but I'm not sure if it answers my question. 
The code on the video is perfect in my case, but I need this extra thing, to not stop counting down when I move to another sheet on the same file. 
Thank you, Natalia
Natalia (rep: 2) Nov 19, '21 at 1:51 am
Natalia. I won't be at my PC fir a few hours yet but please try the answer file in the link I sent (as I recall I took a different approach so the timer should work when other sheets are used or even the file is closed). 
John_Ru (rep: 6102) Nov 19, '21 at 3:56 am
Ok, I'll be able to try it probably in the evening, thank you!
Natalia (rep: 2) Nov 19, '21 at 4:14 am
Hi John, 
to be honest I have a problem to adjust the code that you have written (this with days/hours/mins to this which is in the video. I 'm just starting to learn VBA so I'm not very flexible in coding, rather copying on the beginning, without abilities to adjust much. If you could help what should I add or modify to the code from the video, I would be very grateful. 
Natalia (rep: 2) Nov 19, '21 at 1:00 pm
Hi Natalia

Please see my Answer and attached file
John_Ru (rep: 6102) Nov 19, '21 at 1:22 pm
Add to Discussion

Answers

1
Selected Answer

Natalia

You're right, the tutorial countdown timer stops when you change to another worksheet.

The reason is that the macros in the module refer to Range("A1") and VBA assumes that that cell (in this case A1) is on the active sheet so when you swap sheets it tries to read A1 on the new sheet for example and the macro fails.

The solution is pretty easy- define the worksheet that the timer exists on. In the revised code below, those additions are in bold:

Public interval As Date
Sub timer()
' TeachExcel.com

    ' Check if the timer is finished and exit the macro if it is
    If Sheet1.Range("A1").Value = 0 Then Exit Sub

    ' Remove 1 second from the timer
    Sheet1.Range("A1").Value = Sheet1.Range("A1").Value - TimeValue("00:00:01")

    ' Set when the macro should run again - should be the same time value
    ' as the previous line.
    interval = Now + TimeValue("00:00:01")

    ' Make this macro run again in 1 second
    Application.OnTime interval, "timer"

End Sub
Sub stop_timer()
' TeachExcel.com

    ' Stop the timer macro from running
    Application.OnTime EarliestTime:=interval, Procedure:="timer", Schedule:=False

End Sub
Sub reset_timer()
' TeachExcel.com

    ' Default time for the timer
    Sheet1.Range("A1").Value = "00:05:00"

End Sub
(Note there are various ways to define the worksheet, as you will learn).

Now, when you swap sheets, the timer will keep going (see attached file, and swap to other sheets).

Hope this helps.

Discuss

Discussion

John,
you made my day (or even a week!). This is it. BIG thanks to you. 
I'm motivated to further learning.
Have a good weekend!
Natalia (rep: 2) Nov 19, '21 at 1:36 pm
Glad it worked and thanks for selecting my Answer, Natalia. Have a good weekend too. 
John_Ru (rep: 6102) Nov 19, '21 at 1:50 pm
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login