Create a time-and-motion feature in Excel UserForm

chester1993

New Member
Joined
Jan 26, 2016
Messages
40
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!
 

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type

lrobbo314

Well-known Member
Joined
Jul 14, 2008
Messages
3,286
Office Version
  1. 365
Platform
  1. Windows
The code below seems to be doing what you're asking for.

Book1
ABC
1CBValueTBValueEndTime
21fdsfsd2.63
31fdfdfffd7.41
42fdfdfffd14.94
52fdfdfffd2.73
Tracker


VBA Code:
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

New Member
Joined
Jan 26, 2016
Messages
40
The code below seems to be doing what you're asking for.

Book1
ABC
1CBValueTBValueEndTime
21fdsfsd2.63
31fdfdfffd7.41
42fdfdfffd14.94
52fdfdfffd2.73
Tracker


VBA Code:
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

Well-known Member
Joined
Jul 14, 2008
Messages
3,286
Office Version
  1. 365
Platform
  1. Windows
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:
VBA 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:
VBA 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

New Member
Joined
Jan 26, 2016
Messages
40
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:

VBA Code:
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

VBA Code:
Private Sub CommandButton2_Click()
uDone
End Sub

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

1599035762065.png


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

New Member
Joined
Jan 26, 2016
Messages
40
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.
 

Forum statistics

Threads
1,144,391
Messages
5,724,075
Members
422,535
Latest member
navjeet

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
Top