Create a time-and-motion feature in Excel UserForm

[chester1993]

Hello everyone!

I need your help in creating a time-and-motion using Excel VBA.
Basically, I have a small UserForm which should contain a "Start", "Done" and "Restart" button.

Start = start timer from 00:00:00
Done = End time from 00:00:00
Restart = reset time to 00:00:00

Other than this timer, I would put a Textbox and ComboBox on the same userform.
Every time "Done" is hit I want that time, textbox and combobox value be recorded in Sheet1 named "Tracker". So, data will be recorded every time "Done" is hit and it goes to every row automatically.

The "Tracker" will have multiple columns which are:
A1 to C1 are headers. Then,
A2 = ComboBox1.Value
B2 = TextBox1.Value
C2 = End time

If this takes too much work to do, I apologize in advance. But, kinda need help here.
Thank you so much!

 

[lrobbo314]

The code below seems to be doing what you're asking for.

Book1
	A	B	C
1	CBValue	TBValue	EndTime
2	1	fdsfsd	2.63
3	1	fdfdfffd	7.41
4	2	fdfdfffd	14.94
5	2	fdfdfffd	2.73
Tracker

Dim MS As Double

Private Sub cb_Done_Click()
Dim ws As Worksheet: Set ws = Sheet2
Dim LR As Long: LR = ws.Range("A" & Rows.Count).End(xlUp).Offset(1).Row
ws.Range("A" & LR).Value = Me.ComboBox1
ws.Range("B" & LR).Value = Me.TextBox1
ws.Range("C" & LR).Value = Timer - MS
End Sub

Private Sub cb_Reset_Click()
MS = 0
End Sub

Private Sub cb_Start_Click()
MS = Timer
End Sub

 

[chester1993]

The code below seems to be doing what you're asking for.

Book1
	A	B	C
1	CBValue	TBValue	EndTime
2	1	fdsfsd	2.63
3	1	fdfdfffd	7.41
4	2	fdfdfffd	14.94
5	2	fdfdfffd	2.73
Tracker

Dim MS As Double

Private Sub cb_Done_Click()
Dim ws As Worksheet: Set ws = Sheet2
Dim LR As Long: LR = ws.Range("A" & Rows.Count).End(xlUp).Offset(1).Row
ws.Range("A" & LR).Value = Me.ComboBox1
ws.Range("B" & LR).Value = Me.TextBox1
ws.Range("C" & LR).Value = Timer - MS
End Sub

Private Sub cb_Reset_Click()
MS = 0
End Sub

Private Sub cb_Start_Click()
MS = Timer
End Sub

Thank you so much for this @lrobbo314 !
One quick question, which I think I missed on the initial question, how do I place the running time on a label?

 

[lrobbo314]

I added a label to the userform. I am using the ontime method, so the code needed to be split into a standard module.

Tested it out and it seems to work as expected.

Userform code:

Private Sub cb_Done_Click()
uDone
End Sub

Private Sub cb_Reset_Click()
uReset
End Sub

Private Sub cb_Start_Click()
uStart
End Sub

Standard Module code:

Public MS As Double
Public b As Boolean

Sub uStart()
b = True
MS = Timer
sched
End Sub

Sub uDone()
Dim ws As Worksheet: Set ws = Sheet1
Dim LR As Long: LR = ws.Range("A" & Rows.Count).End(xlUp).Offset(1).Row
ws.Range("A" & LR & ":C" & LR).Value = Array(UserForm1.ComboBox1.Value, UserForm1.TextBox1.Value, Timer - MS)
End Sub

Sub uReset()
MS = 0
b = False
End Sub

Sub sched()
If b Then
    Application.OnTime Now + TimeValue("0:00:01"), "updateTime"
Else
    UserForm1.Label1.Caption = "0"
End If
End Sub

Sub updateTime()
UserForm1.Label1.Caption = Timer - MS
sched
End Sub

 

[chester1993]

Thank you so much @lrobbo314 for your answer. I just started doing this that is why I am only replying now.

Everything seems to be doing okay, except for the uDone function.
I had to add some combobox to the actual file and my code turned out like this:

Sub uDone()
Dim ws As Worksheet: Set ws = Sheet5
Dim LR As Long: LR = ws.Range("A" & Rows.Count).End(xlUp).Offset(1).Row
ws.Range("A" & LR & ":F" & LR).Value = Array(UserForm15.Label6.Caption, UserForm15.ComboBox1.Value, UserForm15.ComboBox2.Value, UserForm15.TextBox1.Value, UserForm15.ComboBox3.Value, Timer - MS)
        
End Sub

Private Sub CommandButton2_Click()
uDone
End Sub

Here is an image of the actual userform with the column where the data should go:

UserForm15.Label6.Caption = Now or Date in the column
UserForm15.ComboBox1.Value = Workflow
UserForm15.ComboBox2.Value = Queue
UserForm15.TextBox1.Value = Acct #
UserForm15.ComboBox3.Value = Status
Timer - MS = Time

So, when I click on Stop, nothings happening. Timer continue to tick.
Also, is there anyway to just show the seconds and minutes on the timer?
What's showing on that image is actually second and millisecond. I think that's too my numbers to record.
I wonder if I can just show it as mm:ss format.

Thanks again!

 

[chester1993]

Oh BTW, on the code "Dim ws As Worksheet: Set ws = Sheet5", is there anyway I can change Sheet5 to the actual sheet name which is Tracker?
I'm worried that users might interchange the arrangement of sheets and mess up the whole thing.