Link to home
Start Free TrialLog in
Avatar of "Abys" Wallace
"Abys" WallaceFlag for United States of America

asked on

Excel VBA: Timer Update

Hello Experts,

I need assistance with a timer feature I have on a userform I created in Excel.  I have cmd buttons:  START, STOP, and RESET on the form that point to certain cells in order to activate the timer.  START = A12,  STOP= B12, and RESET= C12 (sheet:  "Entry_Form" and the time in "h:mm:ss" format is displayed in D12 which populates my textbox= txtTime.

My issue is that the timer works intermittently and I don't know how to correct it or better yet figure out what's causing it to work sometimes and not others.  When I say that it doesn't work I'm meaning that the "txtTime" field doesn't populate and refresh real time once the timer is started.  I see it working fine on the worksheet in the desginated cells but not on the form.

There's 3 pieces of code that makes the timer work:

In the "Workbook":
Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
UpdateTimer

End Sub

Open in new window


Module 1:
Sub Call_Timer()
Application.OnTime Now + TimeValue("00:00:01"), "UpdateTimer"
End Sub

Open in new window


and

Sub UpdateTimer()
If ActiveCell.Address = "$A$12" Then
Range("D12").Value = Range("D12").Value + 1 / 86400
Call_Timer
ElseIf ActiveCell.Address = "$B$12" Then
Exit Sub
ElseIf ActiveCell.Address = "$C$12" Then
Range("D12").Value = 0
End If
End Sub

Open in new window


' code updates txtTime field on form to proper format
Private Sub txtTime_Change()

frmCFSTracker.txtTime = Format(Worksheets("entry_form").Range("d12"), "hh:mm:ss")

End Sub

Open in new window


Control source for "txtTime"= "D12"

NOTE:  When the workbook 1st opens, I enable Macros and select it as a trusted source but no matter what the timer may work or may not work.

Thank you for your help!
Sample-CFS-Tracker2.zip
Avatar of Ryan
Ryan
Flag of United States of America image

I can't make it not work for me, but I suspect its in your UpdateTimer function, where you're checking If ActiveCell.Address = "$A$12" Then

If the focus goes off this cell, then the timer is going to stop counting up...
Avatar of dmitry_n
dmitry_n

Avatar of "Abys" Wallace

ASKER

Not sure why it's not working for me .. Could it because I have my combo boxes pointing to named ranges within the worksheet?   Doesn't make sense to me but I notice that when I don't have my combo boxes RowSource = "namedRange" that the timer works fine.  As soon as I link my combo boxes seems like the functionality of my timer is effected causing it to run intermittently ..

I'm so confused ... I've attached an example with the form's combo boxes linked to named Ranges ...

Thank you for your time
Sample-CFS-Tracker.zip
Another option is to reference current clock time instead of incrementing the time.  Then if it pauses execution it'll correct itself when it reruns.

On Stopwatch Start event
savedTime = now-CurrentStopwatchTime
'CurrentStopwatchTime would be 0 if watch is reset

on timer tick event
Stopwatch = now-SavedTime
@MrBullwinkle thank you for the suggestion but I was really looking for the timer to show on the user form real time.  It used to increment real time fine since I have the textbox "txtTime" control source as "D12" (where the timer is incrementing).  I thought that if you linked a textbox to a cell ref that the text box would automatically display what's in the ref cell...  

Is there a way to refresh the text box "txtTime" every so often automatically without the end user having to pause and restart the timer in order to see how long they've worked a particular task?  

The timer is supposed to calculate how long it takes for the user to complete a task .. And the user should be able to see how far along they've gotten without any further interaction unless they would like to "stop" or "reset" the timer ...
a better question .. is there a way to code the cell reference ... example:  txtTime.value = "D12" and have it auto update within the code?
So the timer is still working, but the form isn't displaying what it's source cell value is...

You could ditch the source cell and link, and instead update the textbox directly...
frmCFSTracker.txtTime.Text = Range("D12").Text
@MrBullwinkle, thank you ... I attempted your suggestion but the field still isn't showing the value of "D12"

I tried:

Private Sub txtTime_Change()

frmCFSTracker.txtTime.Text = Range("D12").Text

End Sub

Open in new window


I also attempted:
Private Sub UserForm_Activate()

frmCFSTracker.txtTime.Text = Range("D12").Text

End Sub

Open in new window


Which populates "txtTime" but it doesn't refresh to show the time incrementing.  Is there a way to refresh "txtTime"?  

When I originally placed the code in my workbook (by itself) it worked great, showing the time as it passed away .. But as soon as I added a little code "txtTime" stopped displaying the value in "D12" .. Just so puzzling.

Thank you again for your time  :)
ASKER CERTIFIED SOLUTION
Avatar of Ryan
Ryan
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Works Perfect!!!  Thank you so much @MrBullWinkle