Adding a Counter in Excel

[Randy11]

Found code to run a counter in a User form. It is based on Time. I simply removed the : between the digits & increased the parameter for roll over to next digit from 59 to 99. This is not quite what I need.
What I would like to do is embed a counter in Excel so that you can continue working while the counter is running. Would like to have two counters. One that rolls up based on number of items processed in the workbook. There is a formula that counts this in real time. The 2nd counter above or below this would like to run based on a set rate of 100 per hour or 500 per hour. i.e. make so the run rate can be altered. The intent is that you can view the planed rate of process vs the actual rate of process. In this case a 5 digit counter would work. The counters would be on the menu page.

Your ideas appreciated.

Public stp As Boolean
Public OldH
Public OldM
Public OldS
Public OLDMLN

Private Sub CommandButton1_Click()
stp = False
CommandButton1.Enabled = False
CommandButton2.Enabled = True
CommandButton3.Enabled = False

H = 0
For M = 0 To 59
For S = 0 To 99
For MLN = 0 To 99
t = Timer
Do Until Timer - t >= 1 / 99
DoEvents
Loop
If stp = True Then GoTo X
Label1.Caption = _
Format(H, "00") & "" & Format(M, "00") _
& "" & Format(S, "00") & "" & Format(MLN, "00")
Next MLN
Next S
Next M
H = H + 1
X:
OldH = H
OldM = M
OldS = S
OLDMLN = MLN
stp = False
End Sub

Private Sub CommandButton2_Click()
CommandButton1.Enabled = True
CommandButton2.Enabled = False
CommandButton3.Enabled = True
stp = True
End Sub

Private Sub CommandButton3_Click()
CommandButton3.Enabled = False
CommandButton2.Enabled = True
CommandButton1.Enabled = False

stp = False
H = OldH
For M = OldM To 99
For S = OldS To 99
For MLN = OLDMLN To 99
t = Timer
Do Until Timer - t >= 1 / 99
DoEvents
Loop
If stp = True Then GoTo X
Label1.Caption = _
Format(H, "00") & "" & Format(M, "00") _
& "" & Format(S, "00") & "" & Format(MLN, "00")
Next MLN
Next S
Next M
H = H + 1
X:
OldH = H
OldM = M
OldS = S
OLDMLN = MLN
stp = False
End Sub

Private Sub CommandButton4_Click()
Unload Me
End
End Sub


Private Sub UserForm_Initialize()
CommandButton1.Enabled = True
CommandButton1.Caption = "Start Timer"
CommandButton2.Enabled = False
CommandButton2.Caption = "Stop"
CommandButton3.Enabled = False
CommandButton3.Caption = "Resume Timer"
CommandButton4.Caption = "Cancel"
Label1.Caption = "00000000"
End Sub

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
If CloseMode = 0 Then Cancel = True
End Sub

In Excel place a command button with the following code in it.
Private Sub CommandButton1_Click()
UserForm1.Show
End Sub

 

[SkipVought]

Hi,

Timer counts in seconds, I believe.
So simply convert to hours for you target rates.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Randy11]

Hi Skip, The code sample I provided is designed for use in a form. When form is open, you are unable to process in the workbook. Am looking for a way to embed the counters into the workbook.
Am hoping the response isn't a big belly laugh... Ho Ho Ho....
Looks like clocks or counters in excel is problematic. Put a clock in to test & had screen flashing & stalling each second when it updated.... If anyone is aware of good links to these types of options, that would also be helpful....

 

[SkipVought]

rather than this

Label1.Caption = _
                    Format(H, "00") & "" & Format(M, "00") _
                    & "" & Format(S, "00") & "" & Format(MLN, "00")

something like this

Sheets(1).Range("A1") = _
                    Format(H, "00") & "" & Format(M, "00") _
                    & "" & Format(S, "00") & "" & Format(MLN, "00")

I would convert the timer value to DAYS and use ONE format function like

Sheets(1).Range("A1") = Format(t / 60 / 60 / 24, "hh:mm:ss")

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}