Timer made better (code)

jelly77

New Member
Joined
Aug 3, 2017
Messages
8
Hello everyone
great forum (first time poster)
i have a spreadsheet that has a userform built in to it that when the form is opened it starts a timer, the form is completed and on exiting the timer stops and calculates the time the userform is open. when closed the form populates certain cells on the sheet. however the process of closing the form and populating cells seems to take a while, i have 2 questions 1 how can i make this process quicker and 2 can i have it so the coding will allow more than 1 person to access and use it (i have inherited this and think it may be a bodge code)
the first code below is in a module and the second is in the userform.

Any help would be greatly appreciated.

Sub auto_open_userform1()
Worksheets("Inputs").Activate
End Sub
Sub uform()
a = Time()

With UserForm1
.Label6 = a
End With
UserForm1.Show
End Sub
Sub gsd()
end_date = Worksheets("Inputs").Cells(3, 4)
start_date = Worksheets("Inputs").Cells(3, 3)
ans = end_date - start_date
a = Format(ans, "HH:MM:SS")
MsgBox a
End Sub
**********************************************
Sub gsd()
end_date = Worksheets("Inputs").Cells(3, 4)
start_date = Worksheets("Inputs").Cells(3, 3)
ans = end_date - start_date
a = Format(ans, "HH:MM:SS")
MsgBox a
End Sub
Private Sub CommandButton1_Click()
'add data to sheet
For i = 2 To 65000
If Worksheets("Inputs").Cells(i, 1) = "" Then
t = i
i = 65000
End If
Next i


a = UserForm1.ComboBox1.Value
Worksheets("Inputs").Cells(t, 1) = a
b = UserForm1.ComboBox2.Value
Worksheets("Inputs").Cells(t, 2) = b
c = UserForm1.TextBox1.Value
Worksheets("Inputs").Cells(t, 3) = c
d = UserForm1.ComboBox3.Value
Worksheets("Inputs").Cells(t, 4) = d
e = UserForm1.ComboBox4.Value
Worksheets("Inputs").Cells(t, 5) = e
f = UserForm1.DTPicker2.Value
Worksheets("Inputs").Cells(t, 6) = f
g = UserForm1.DTPicker3.Value
Worksheets("Inputs").Cells(t, 7) = g
h = UserForm1.ComboBox5.Value
Worksheets("Inputs").Cells(t, 8) = h
i = UserForm1.TextBox4.Value
Worksheets("Inputs").Cells(t, 9) = i


If a > "" Then
Worksheets("Inputs").Cells(t, 1) = a
End If
If b > "" Then
Worksheets("Inputs").Cells(t, 2) = b
End If
If c > "" Then
Worksheets("Inputs").Cells(t, 3) = c
End If
If d > "" Then
Worksheets("Inputs").Cells(t, 4) = d
End If
If e > "" Then
Worksheets("Inputs").Cells(t, 5) = e
End If
If f > "" Then
Worksheets("Inputs").Cells(t, 6) = f
End If
If g > "" Then
Worksheets("Inputs").Cells(t, 7) = g
End If
If h > "" Then
Worksheets("Inputs").Cells(t, 8) = h
End If
If i > "" Then
Worksheets("Inputs").Cells(t, 9) = i
End If










a = UserForm1.Label6
Worksheets("Inputs").Cells(t, 10) = a
a = Time
s = Format(a, "HH:MM:SS")
a = s
Worksheets("Inputs").Cells(t, 11) = a
end_date = Worksheets("Inputs").Cells(t, 11)
start_date = Worksheets("Inputs").Cells(t, 10)
ans = end_date - start_date
a = Format(ans, "HH:MM:SS")
Worksheets("Inputs").Cells(t, 12) = a
UserForm1.Hide
UserForm1.ComboBox1.Value = ""
UserForm1.ComboBox2.Value = ""
UserForm1.ComboBox3.Value = ""
UserForm1.ComboBox4.Value = ""
UserForm1.ComboBox5.Value = ""
UserForm1.TextBox1.Value = ""
UserForm1.TextBox4.Value = ""


End Sub


Private Sub CommandButton2_Click()
UserForm1.Hide
UserForm1.ComboBox1.Value = ""
UserForm1.ComboBox2.Value = ""
UserForm1.ComboBox3.Value = ""
UserForm1.ComboBox4.Value = ""
UserForm1.ComboBox5.Value = ""
UserForm1.TextBox1.Value = ""
UserForm1.TextBox4.Value = ""




End Sub



Many thanks
 

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK
.
Code:
Option Explicit


Sub howLong()
     
    Dim tStart As Double, tEnd As Double
     
    tStart = Timer
     
     '
     'your whole procedure here
     '
     
    tEnd = Timer
     
    MsgBox "Completed in:  " & ((tEnd - tStart) \ 60) & " minutes, " & ((tEnd - tStart) Mod 60) & " seconds."
     
End Sub

The above goes in the UserForm.

Re: more than one user accessing the workbook / userform at the same time. Not so easy with Excel. It was not designed for more than one user.
If your project allows, you can give each user their own copy of the same workbook - you retain a MASTER WORKBOOK that periodically reviews all the other copies and
obtains new information there to be copied back to the MASTER.

Another option would be to use Microsoft's CLOUD service where you can post your workbook online and more than one person can access it at the same time.
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,213
Members
448,554
Latest member
Gleisner2

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