Excel Timer Macro overwriting data in active worksheets

sbraemer · Feb 17, 2014

I am running a program that when a ‘Start’ command button is clicked, a timer begins counting in seconds and displays the value in cell B3 until a ‘Stop’ command button is clicked. The event works flawlessly as long as I don’t open up another workbook (to continue working in Excel). When the second workbook is open…then time stops working in the workbook where the macro was created…and starts displaying the ‘timer count in seconds’ in the new active workbook. When I go back to the original book that called the macro…it continues counting again. I need this timer to continue running in the workbook with the command buttons (in the background) while I work on other worksheets in other books. Is there a way to keep the timer running no matter how other workbooks are opened and prevent it from overwriting whatever sheet is active? This code below ‘overwrites’ the contents of cell B3 on whatever sheet is active---and stops the timer in the on the sheet with the ‘Start’ command button. Anyone willing to review this code, your time and efforts are greatly appreciated.

Code:

Option Explicit

   
  Sub OnTimeMacro()
      Application.OnTime (Now + TimeValue("00:00:01")), "RunEveryMinute"
  End Sub
   
  Sub OnTimeMacro2()
      Application.OnTime (Now + TimeValue("00:00:01")), "StopEveryMinute"
  End Sub
   
  Sub RunEveryMinute()
  With Worksheets(1)
      Range("J3").Value = Format(Now, "hh:mm:ss")
  End With
  End Sub
   
  Sub StopEveryMinute()
  With Worksheets(1)
      Range("K3").Value = Format(Now, "hh:mm:ss")
  End With
  End Sub
   
  Sub startTimer1()
      Range("J3") = 0
      Range("K3") = 0
      Call OnTimeMacro
      Counting = True
      Call Increment1
  End Sub
   
  Sub Increment1()
  If Counting Then
      Range("B3") = Range("B3") + 1
      Application.OnTime (Now + TimeValue("00:00:01")), "Increment1"
  End If
  End Sub
   
  Sub stopTimer1()
      Call OnTimeMacro2
      Counting = False
  End Sub

   
  Sub resetTimer1()
      Range("B3") = 0
      Range("J3") = 0
      Range("K3") = 0
  End Sub

ChrisM · Feb 17, 2014

You have a couple issues with your code. First,

Code:

Sub RunEveryMinute()
  With Worksheets(1)
      Range("J3").Value = Format(Now, "hh:mm:ss")
  End With
  End Sub

Using Worksheets(1) is pretty dangerous. If someone were to reorder your tabs then sheet #1 will be different. It would be much better to just name the sheet, like Worksheets("mysheet")
You have not put a period in front of Range("J3"). No period means it will default to the ActiveSheet. You're really not getting any use out of your With-End With because of this. And for that matter, using With-End With with only one line of code doesn't do much for you, you could rewrite all three lines as just:

Worksheets(1).Range("J3").Value = Format(Now, "hh:mm:ss")

In a similar issue, note everywhere in your code you use just "Range(x) = xxx". Again, you are using the Range object with no parent worksheet, which means it is defaulting to the current active sheet.

Now on to your specific problem. All you need to do is correctly reference the 1) workbook 2)worksheet and 3) range every time you want to write your time value. You can write tidier code by assigning those values to various objects, or you can just write it all out like this:

Code:

Sub RunEveryMinute()
     Workbooks("Myworkbook.xlsm").Worksheets("myworksheetname").Range("J3").Value = Format(Now, "hh:mm:ss")
 End Sub

**Edit

Actually, it would be better to write it like this, that way you can change your file name:

Code:

Sub RunEveryMinute()
     ThisWorkbook.Worksheets("myworksheetname").Range("J3").Value = Format(Now, "hh:mm:ss")
 End Sub

(thisworkbook refers to the workbook in which the VBA code is running, which will always match the destination you want)

sbraemer · Feb 17, 2014

Thank you Chris for your quick reply. You're absolutely right about the danger in using Worksheets(1) in the subs. They are all explicitly referenced now. I have to say I didn't think this problem was solvable...but, your code was simple, efficient, and worked beautifully!! All problems have been solved: Counter increments on parent sheet, doesn't overwrite cell values on newly active sheets, and runs continuously while working on other worksheets--code follows below. I'm impressed with your response...thank you for taking the time to share your expertise. I truly appreciate it! Now I just have to make the corrections on 120 command buttons and get the screen flickering under control. ~sbraemer

Code:

Sub Increment1()
Application.ScreenUpdating = False
If Counting Then
           ThisWorkbook.Worksheets("Timer").Range("B3") = ThisWorkbook.Worksheets("Timer").Range("B3") + 1
           Application.OnTime (Now + TimeValue("00:00:01")), "Increment1"
End If
Application.ScreenUpdating = True
End Sub

ChrisM · Feb 17, 2014

Glad to help. I don't think you'll be able to solve the flickering issue if ScreenUpdating isn't helping. I'm assuming it's flickering when you are in file #2 and it's writing the timer value in file #1?

Do you need to timer value listed in a cell for a particular reason? If not, what if you just write it to the status bar using "application.status bar = now()". That wouldn't cause a flicker.

Or, if you must write to the cell, and you get flickering in the second file, you could write code to NOT write the current time until/when the original workbook is the ActiveWorkbook.

sbraemer · Feb 17, 2014

Sorry it took so long to reply back…I just made all of the changes to the code to get all the counters to work properly.

Yes, the screen flickering is annoying when I’m in file #2. Compounding the flickering problem…I have 40 Start Command Buttons in the ‘B’ column…all running counters. They are keeping track of the time elapsed when specific job functions are being performed. The timer results are displayed in the B column.

As long as the timer is running…I don’t need the seconds to be constantly written to the screen. (I’m doing a time elapsed calculation on the actual sheet in the A column by picking up the timer’s value from the B column [ A3 = B3/(24*60*60)]. However, I don’t know if writing to the status bar using the “application.status bar = now()” will work when 40 macros are running at the same time.

Maybe writing code to NOT write the current time until the original workbooks is activated is the way to go. Do you know if it’s the actual processing being done by the macro that’s causing the flickering…or is it the actual ‘real-time’ displaying it that’s causing the flickering problem? The ScreenUpdating doesn’t seem to be doing much of anything.

ChrisM · Feb 17, 2014

If you don't need to see the real time seconds counting by, then you've gone in the wrong direction. All you need to do is create a module-level or Static double array variable. By doing that the variable will never go out of scope so you'll always have access to it. Depending on if you want to track both start and stop time, create either a 1 or 2 dimensional array. Let's say you want to track both.

In the "ThisWorkbook" module, at the top, add this line:

Public aMyTimes(1 to 40, 1 to 2) as Double

Now you should have subs for all 40 of your command buttons. You need to come up with a numbering scheme to relate each button # to it's spot in the 40 slots of the aMyTimes array. Here's one way when you click the start button:

Code:

Sub CommandButton1_Click

   Dim i as Long

   i  = 1
   aMyTimes(i,1) = Now()
End Sub

Now the Stop button:

Code:

Sub CommandButton2_Click

  Dim i as Long
  i = 1
  AMyTimes(i,2) = Now()

  DestinationWorksheet.Range("A1") = aMyTimes(i,2) - aMyTimes(i,1)
End Sub

Does that make sense? You would then copy that 2-block sub 39 more times, and change i from 2 to 39. You don't really even need to use i, I just thought it would be clearer for you.

If you've used Activex command buttons, you can even use the Tag property to store the number, and get away from i all together.

sbraemer · Feb 17, 2014

Although I don’t need to see the real time seconds displayed on the screen, a counter does need to be running. At certain intervals, charges begin to accrue ie. after 120 minutes. I have the sheet set up to display a message in a particular column when the counter trips it ie. at 90 minutes a warning appears. So I think I need an actual dynamic second counter. I don’t think a static two-dimensional array would work. However, I am displaying a static Start and Stop time in other columns using this code:

Code:

Sub RunEveryMinute()
      ThisWorkbook.Worksheets("Timer").Range("J3").Value = Format(Now, "hh:mm:ss")
 End Sub
   
 Sub StopEveryMinute()
      ThisWorkbook.Worksheets("Timer").Range("K3").Value = Format(Now, "hh:mm:ss")
 End Sub

However, this is only providing a Start and Stop time stamp. I need a warning message at 90 minutes and begin calculating charges at 120 minutes. I have all of this working correctly based on the timer in the B column. The purpose of the sheet is to track the time elapsed for 40 functions and trip warnings and perform calculations when it hits the trigger ie. 120 minutes.

 I have used ActiveX Command buttons. One for each of the 3 actions:startTimer1, stopTimer1, and resetTimer1 (X’s 40 or 120 Command Buttons total). I’m not sure what you mean by the Tag property to store the number.

Code:

Private Sub CommandButton1_Click()
   startTimer1
End Sub

Private Sub CommandButton2_Click()
  stopTimer1
End Sub

Private Sub CommandButton3_Click()
   resetTimer1
End Sub

I was really hoping I was doing this in the simplest way possible, but you’re making me re-think my approach…I appreciate all of your guidance. You've certainly provided a great deal of help. ~sbraemer

ChrisM said:
If you don't need to see the real time seconds counting by, then you've gone in the wrong direction. All you need to do is create a module-level or Static double array variable. By doing that the variable will never go out of scope so you'll always have access to it. Depending on if you want to track both start and stop time, create either a 1 or 2 dimensional array. Let's say you want to track both.

In the "ThisWorkbook" module, at the top, add this line:

Public aMyTimes(1 to 40, 1 to 2) as Double

Now you should have subs for all 40 of your command buttons. You need to come up with a numbering scheme to relate each button # to it's spot in the 40 slots of the aMyTimes array. Here's one way when you click the start button:

Code:

Sub CommandButton1_Click Dim i as Long i = 1 aMyTimes(i,1) = Now() End Sub

Now the Stop button:

Code:

Sub CommandButton2_Click Dim i as Long i = 1 AMyTimes(i,2) = Now() DestinationWorksheet.Range("A1") = aMyTimes(i,2) - aMyTimes(i,1) End Sub

Does that make sense? You would then copy that 2-block sub 39 more times, and change i from 2 to 39. You don't really even need to use i, I just thought it would be clearer for you.

If you've used Activex command buttons, you can even use the Tag property to store the number, and get away from i all together.

sbraemer · Feb 17, 2014

ChrisM: Thank you again for your viewing my code regarding parent sheet references to prevent overwriting values in subsequent active worksheets. I found that I also needed to remove all of the Application.ScreenUpdating = False references in the increment modules. As soon as I removed them, the flickering stopped completely. Everything looks great and works properly. Thank you again for your help. ~sbraemer

sbraemer said:
 Although I don’t need to see the real time seconds displayed on the screen, a counter does need to be running. At certain intervals, charges begin to accrue ie. after 120 minutes. I have the sheet set up to display a message in a particular column when the counter trips it ie. at 90 minutes a warning appears. So I think I need an actual dynamic second counter. I don’t think a static two-dimensional array would work. However, I am displaying a static Start and Stop time in other columns using this code:

Code:

Sub RunEveryMinute() ThisWorkbook.Worksheets("Timer").Range("J3").Value = Format(Now, "hh:mm:ss") End Sub Sub StopEveryMinute() ThisWorkbook.Worksheets("Timer").Range("K3").Value = Format(Now, "hh:mm:ss") End Sub

However, this is only providing a Start and Stop time stamp. I need a warning message at 90 minutes and begin calculating charges at 120 minutes. I have all of this working correctly based on the timer in the B column. The purpose of the sheet is to track the time elapsed for 40 functions and trip warnings and perform calculations when it hits the trigger ie. 120 minutes.

 I have used ActiveX Command buttons. One for each of the 3 actions:startTimer1, stopTimer1, and resetTimer1 (X’s 40 or 120 Command Buttons total). I’m not sure what you mean by the Tag property to store the number.

Code:

Private Sub CommandButton1_Click() startTimer1 End Sub Private Sub CommandButton2_Click() stopTimer1 End Sub Private Sub CommandButton3_Click() resetTimer1 End Sub



I was really hoping I was doing this in the simplest way possible, but you’re making me re-think my approach…I appreciate all of your guidance. You've certainly provided a great deal of help. ~sbraemer

Excel Timer Macro overwriting data in active worksheets

sbraemer

New Member

Excel Facts

ChrisM

Well-known Member

sbraemer

New Member

ChrisM

Well-known Member

sbraemer

New Member

ChrisM

Well-known Member

sbraemer

New Member

sbraemer

New Member

Similar threads

Forum statistics

Share this page

Excel Timer Macro overwriting data in active worksheets

New Member

Excel Facts

Well-known Member

New Member

Well-known Member

New Member

Well-known Member

New Member

New Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock