?
Solved

Excel VBA:  Timer Update

Posted on 2012-08-17
10
Medium Priority
?
674 Views
Last Modified: 2012-08-20
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
0
Comment
Question by:"Abys" Wallace
  • 5
  • 4
10 Comments
 
LVL 13

Expert Comment

by:Ryan
ID: 38306889
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...
0
 
LVL 3

Expert Comment

by:dmitry_n
ID: 38307352
0
 

Author Comment

by:"Abys" Wallace
ID: 38312138
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
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 13

Expert Comment

by:Ryan
ID: 38312217
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
0
 

Author Comment

by:"Abys" Wallace
ID: 38312455
@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 ...
0
 

Author Comment

by:"Abys" Wallace
ID: 38312465
a better question .. is there a way to code the cell reference ... example:  txtTime.value = "D12" and have it auto update within the code?
0
 
LVL 13

Expert Comment

by:Ryan
ID: 38313015
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
0
 

Author Comment

by:"Abys" Wallace
ID: 38313163
@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  :)
0
 
LVL 13

Accepted Solution

by:
Ryan earned 2000 total points
ID: 38313330
Wrong event. It should go here.

Sub UpdateTimer()
If ActiveCell.Address = "$A$12" Then
Range("D12").Value = Range("D12").Value + 1 / 86400
           frmCFSTracker.txtTime.Text = Range("D12").Text
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

0
 

Author Comment

by:"Abys" Wallace
ID: 38313353
Works Perfect!!!  Thank you so much @MrBullWinkle
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
This article describes a serious pitfall that can happen when deleting shapes using VBA.
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question