Trying to create a running counter in Excel (Forms)

M

mbrittb

Board Regular
Joined
Jul 24, 2002
Messages
62
I am tring to create a running counter/timer form in Excel using visual basic macros. I have been able to develope a counter that up until a specified time then stops. What I really need is a counter that will continue to count up until the form is exited. I also need the ability to reset the counter at any time. On other thing I need to be able to interact with another form at the same time (could be the one that spawns the counter form) or other parts of the same form (in this case I would be making the counter part of the main form rather than opening up a different form). I know this is a complicated problem, which may not have an answer but any info would help.

Thanks,

Britt
 

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.
Sort by date Sort by votes
mbrittb,

Do you want a form with the ability to count until it is unloaded?
What is it counting? The number of occurrances of what?

Or do you want a form with the abiliy to measure the time that it is displayed (in seconds or minutes or What?)?

Do you want this form to time or count from the moment that it is displayed and also be capable of having its time or count reset to zero? What event would cause this to occur?

Post the counter that you have developed and an explanation of how this form would interact with other forms, including "the one that spawns" it.

Bob
 
Upvote 0
I am trying to create a counter that will display a time offset from some fixed time. I would like to be able to reset this base (fixed) time at the click of a button thus resetting the counter to zero and starting over. The counter I have developed thus far simplay displays the time offset from a fixed time, but there is no way to interrupt it, until it reaches a predefined limit. The more that I think about it, my real question is is there a way to have focus control on two different Excel VBA Forms at the same time. Or have control over the objects on one form while a VBA macro is running.

Another option would be to activate the counter form (by clicking on a button in another form) the return control back to the previous form. The problem is I can't figure out how to return control to the previous form, and leave the other form running in the background.

Britt
 
Upvote 0
Hi,

Create a UserForm1and add to it the following Controls:

Label1 to Display the Timer.
CommandButton1 to Start the Timer afresh
CommandButton2 to Stop the Timer
CommandButton3 to Resume the Timer
CommandButton4 to Cancel the UserForm.

After Crating the UserForm,paste the following Code in its Module:

Code: 
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 59
              For MLN = 0 To 59
                    t = Timer
                    Do Until Timer - t >= 1 / 60
                        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 59
            For S = OldS To 59
              For MLN = OLDMLN To 59
                    t = Timer
                    Do Until Timer - t >= 1 / 60
                        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 = "00:00:00:00"
End Sub

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


Now embeed a Button'CommandButton1' in a worksheet to call the UserForm,Double Click it and Paste the Following Code :

Code: 
Private Sub CommandButton1_Click()
    UserForm1.Show
End Sub


Hope this Helps.
 
Upvote 0
Thanks for the code for the timer... I want to try to take it one step further.

I am working on an energy spreadsheet to be able to show students, faculty, etc. how much energy they use given a certain set of parameters. I want to give the user an opportunity to "turn on" lights, stereo, ipod, microwave, etc., and have the ability for the spreadsheet to show how quickly (or slowly) kilowatt hours (or $) add up over a certain amount of time. Ideally there is a form that allows the user to input the parameters, then a second form that shows the timer counter that counts kWh and $ over the period of time that the user watches the spreadsheet. I can embed the data (wattage, kWh rate, etc.) but I don't know how to perform the math within the user form.

Thanks!

Bill Davis
bill_davis@ncsu.edu
 
Upvote 0
You must log in or register to reply here.

Similar threads

V
  • Question
VBA to automatically save and close an Excel file after a period of inactivity in the file
Replies
1
Views
1K
Logit
Logit
T
  • Question
Run-time error '429': ActiveX component can't create object
Replies
0
Views
228
TheSardOz
T
D
  • Question
Trying to create a results sheet using two data ranges
Replies
0
Views
168
Doot
D
N
  • Question
2-step Dynamic Ranges and Charts
Replies
0
Views
201
Nick Loughton
N
D
  • Question
Issue with EntireRow.Delete
2
Replies
11
Views
804
RoryA
RoryA

Forum statistics

Threads
1,214,983
Messages
6,122,591
Members
449,089
Latest member
Motoracer88

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top