Sum Combo Boxes to get total time worked

S

sacastiglia

New Member
Joined
Jul 29, 2014
Messages
23
Hello,

I will be so grateful to get this solved. I have a user form (UserForm1) that I need to calculate total amount of hours worked in a text box from the values in the combo boxes below. All values are time based (i.e. 7:00 am, 7:00 pm; not military time)

ComboBox2 = Drive Start Time
ComboBox3 = On Site Time
ComboBox4 = Break Start Time
ComboBox5 = Break End Time
ComboBox6 = Off Site Time
ComboBox7 = Drive End Time

I have a formula in the timesheet to calculate this but that won't happen until the user form is loaded into the spreadsheet. I would like to have this feature available to the users so if a mistake is made, they can correct it before loading it onto the timesheet.

The code I have for the initialize user form is as follows:


HTML: 
Private Sub UserForm_Initialize()
OptionButton1.Value = True
OptionButton2.Value = True
OptionButton3.Value = True
OptionButton4.Value = True
OptionButton5.Value = True
OptionButton12.Value = True
OptionButton13.Value = True
OptionButton14.Value = True
OptionButton9.Value = True
OptionButton10.Value = True
TextBox2.Value = ""
TextBox15.Value = (Val(ComboBox3.Value) + Val(ComboBox2.Value)) * 24
With ComboBox1
.CLEAR
.AddItem "Bereavement"
.AddItem "Holiday"
.AddItem "Jury Duty"
.AddItem "Light Duty"
.AddItem "On Call"
.AddItem "Prevailing Wage - Mgr"
.AddItem "Prevailing Wage - Non Mgr"
.AddItem "Prevailing Wage Hours"
.AddItem "Regular"
.AddItem "Ride Time"
.AddItem "Sick"
.AddItem "Training"
.AddItem "Vacation"
End With
With ComboBox2
.CLEAR
.AddItem "12:00 AM"
.AddItem "12:30 AM"
.AddItem "12:45 AM"
.AddItem "1:00 AM"
.AddItem "1:15 AM"
.AddItem "1:30 AM"
.AddItem "1:45 AM"
.AddItem "2:00 AM"
.AddItem "2:15 AM"
.AddItem "2:30 AM"
.AddItem "2:45 AM"
.AddItem "3:00 AM"
.AddItem "3:15 AM"
.AddItem "3:30 AM"
.AddItem "3:45 AM"
.AddItem "4:00 AM"
.AddItem "4:15 AM"
.AddItem "4:30 AM"
.AddItem "4:45 AM"
.AddItem "5:00 AM"
.AddItem "5:15 AM"
.AddItem "5:30 AM"
.AddItem "5:45 AM"
.AddItem "6:00 AM"
.AddItem "6:15 AM"
.AddItem "6:30 AM"
.AddItem "6:45 AM"
.AddItem "7:00 AM"
.AddItem "7:15 AM"
.AddItem "7:30 AM"
.AddItem "7:45 AM"
.AddItem "8:00 AM"
.AddItem "8:15 AM"
.AddItem "8:30 AM"
.AddItem "8:45 AM"
.AddItem "9:00 AM"
.AddItem "9:15 AM"
.AddItem "9:30 AM"
.AddItem "9:45 AM"
.AddItem "10:00 AM"
.AddItem "10:15 AM"
.AddItem "10:30 AM"
.AddItem "10:45 AM"
.AddItem "11:00 AM"
.AddItem "11:45 AM"
.AddItem "12:00 PM"
.AddItem "12:15 PM"
.AddItem "12:30 PM"
.AddItem "12:45 PM"
.AddItem "1:00 PM"
.AddItem "1:15 PM"
.AddItem "1:30 PM"
.AddItem "1:45 PM"
.AddItem "2:00 PM"
.AddItem "2:15 PM"
.AddItem "2:30 PM"
.AddItem "2:45 PM"
.AddItem "3:00 PM"
.AddItem "3:15 PM"
.AddItem "3:30 PM"
.AddItem "3:45 PM"
.AddItem "4:00 PM"
.AddItem "4:15 PM"
.AddItem "4:30 PM"
.AddItem "4:45 PM"
.AddItem "5:00 PM"
.AddItem "5:15 PM"
.AddItem "5:30 PM"
.AddItem "5:45 PM"
.AddItem "6:00 PM"
.AddItem "6:15 PM"
.AddItem "6:30 PM"
.AddItem "6:45 PM"
.AddItem "7:00 PM"
.AddItem "7:15 PM"
.AddItem "7:30 PM"
.AddItem "7:45 PM"
.AddItem "8:00 PM"
.AddItem "8:15 PM"
.AddItem "8:30 PM"
.AddItem "8:45 PM"
.AddItem "9:00 PM"
.AddItem "9:15 PM"
.AddItem "9:30 PM"
.AddItem "9:45 PM"
.AddItem "10:00 PM"
.AddItem "10:15 PM"
.AddItem "10:30 PM"
.AddItem "10:45 PM"
.AddItem "11:00 PM"
.AddItem "11:15 PM"
.AddItem "11:30 PM"
.AddItem "11:45 PM"
.AddItem "11:59 PM"
End With
With ComboBox3
.CLEAR
.AddItem "12:00 AM"
.AddItem "12:30 AM"
.AddItem "12:45 AM"
.AddItem "1:00 AM"
.AddItem "1:15 AM"
.AddItem "1:30 AM"
.AddItem "1:45 AM"
.AddItem "2:00 AM"
.AddItem "2:15 AM"
.AddItem "2:30 AM"
.AddItem "2:45 AM"
.AddItem "3:00 AM"
.AddItem "3:15 AM"
.AddItem "3:30 AM"
.AddItem "3:45 AM"
.AddItem "4:00 AM"
.AddItem "4:15 AM"
.AddItem "4:30 AM"
.AddItem "4:45 AM"
.AddItem "5:00 AM"
.AddItem "5:15 AM"
.AddItem "5:30 AM"
.AddItem "5:45 AM"
.AddItem "6:00 AM"
.AddItem "6:15 AM"
.AddItem "6:30 AM"
.AddItem "6:45 AM"
.AddItem "7:00 AM"
.AddItem "7:15 AM"
.AddItem "7:30 AM"
.AddItem "7:45 AM"
.AddItem "8:00 AM"
.AddItem "8:15 AM"
.AddItem "8:30 AM"
.AddItem "8:45 AM"
.AddItem "9:00 AM"
.AddItem "9:15 AM"
.AddItem "9:30 AM"
.AddItem "9:45 AM"
.AddItem "10:00 AM"
.AddItem "10:15 AM"
.AddItem "10:30 AM"
.AddItem "10:45 AM"
.AddItem "11:00 AM"
.AddItem "11:45 AM"
.AddItem "12:00 PM"
.AddItem "12:15 PM"
.AddItem "12:30 PM"
.AddItem "12:45 PM"
.AddItem "1:00 PM"
.AddItem "1:15 PM"
.AddItem "1:30 PM"
.AddItem "1:45 PM"
.AddItem "2:00 PM"
.AddItem "2:15 PM"
.AddItem "2:30 PM"
.AddItem "2:45 PM"
.AddItem "3:00 PM"
.AddItem "3:15 PM"
.AddItem "3:30 PM"
.AddItem "3:45 PM"
.AddItem "4:00 PM"
.AddItem "4:15 PM"
.AddItem "4:30 PM"
.AddItem "4:45 PM"
.AddItem "5:00 PM"
.AddItem "5:15 PM"
.AddItem "5:30 PM"
.AddItem "5:45 PM"
.AddItem "6:00 PM"
.AddItem "6:15 PM"
.AddItem "6:30 PM"
.AddItem "6:45 PM"
.AddItem "7:00 PM"
.AddItem "7:15 PM"
.AddItem "7:30 PM"
.AddItem "7:45 PM"
.AddItem "8:00 PM"
.AddItem "8:15 PM"
.AddItem "8:30 PM"
.AddItem "8:45 PM"
.AddItem "9:00 PM"
.AddItem "9:15 PM"
.AddItem "9:30 PM"
.AddItem "9:45 PM"
.AddItem "10:00 PM"
.AddItem "10:15 PM"
.AddItem "10:30 PM"
.AddItem "10:45 PM"
.AddItem "11:00 PM"
.AddItem "11:15 PM"
.AddItem "11:30 PM"
.AddItem "11:45 PM"
.AddItem "11:59 PM"
End With
With ComboBox4
.CLEAR
.AddItem "12:00 AM"
.AddItem "12:30 AM"
.AddItem "12:45 AM"
.AddItem "1:00 AM"
.AddItem "1:15 AM"
.AddItem "1:30 AM"
.AddItem "1:45 AM"
.AddItem "2:00 AM"
.AddItem "2:15 AM"
.AddItem "2:30 AM"
.AddItem "2:45 AM"
.AddItem "3:00 AM"
.AddItem "3:15 AM"
.AddItem "3:30 AM"
.AddItem "3:45 AM"
.AddItem "4:00 AM"
.AddItem "4:15 AM"
.AddItem "4:30 AM"
.AddItem "4:45 AM"
.AddItem "5:00 AM"
.AddItem "5:15 AM"
.AddItem "5:30 AM"
.AddItem "5:45 AM"
.AddItem "6:00 AM"
.AddItem "6:15 AM"
.AddItem "6:30 AM"
.AddItem "6:45 AM"
.AddItem "7:00 AM"
.AddItem "7:15 AM"
.AddItem "7:30 AM"
.AddItem "7:45 AM"
.AddItem "8:00 AM"
.AddItem "8:15 AM"
.AddItem "8:30 AM"
.AddItem "8:45 AM"
.AddItem "9:00 AM"
.AddItem "9:15 AM"
.AddItem "9:30 AM"
.AddItem "9:45 AM"
.AddItem "10:00 AM"
.AddItem "10:15 AM"
.AddItem "10:30 AM"
.AddItem "10:45 AM"
.AddItem "11:00 AM"
.AddItem "11:45 AM"
.AddItem "12:00 PM"
.AddItem "12:15 PM"
.AddItem "12:30 PM"
.AddItem "12:45 PM"
.AddItem "1:00 PM"
.AddItem "1:15 PM"
.AddItem "1:30 PM"
.AddItem "1:45 PM"
.AddItem "2:00 PM"
.AddItem "2:15 PM"
.AddItem "2:30 PM"
.AddItem "2:45 PM"
.AddItem "3:00 PM"
.AddItem "3:15 PM"
.AddItem "3:30 PM"
.AddItem "3:45 PM"
.AddItem "4:00 PM"
.AddItem "4:15 PM"
.AddItem "4:30 PM"
.AddItem "4:45 PM"
.AddItem "5:00 PM"
.AddItem "5:15 PM"
.AddItem "5:30 PM"
.AddItem "5:45 PM"
.AddItem "6:00 PM"
.AddItem "6:15 PM"
.AddItem "6:30 PM"
.AddItem "6:45 PM"
.AddItem "7:00 PM"
.AddItem "7:15 PM"
.AddItem "7:30 PM"
.AddItem "7:45 PM"
.AddItem "8:00 PM"
.AddItem "8:15 PM"
.AddItem "8:30 PM"
.AddItem "8:45 PM"
.AddItem "9:00 PM"
.AddItem "9:15 PM"
.AddItem "9:30 PM"
.AddItem "9:45 PM"
.AddItem "10:00 PM"
.AddItem "10:15 PM"
.AddItem "10:30 PM"
.AddItem "10:45 PM"
.AddItem "11:00 PM"
.AddItem "11:15 PM"
.AddItem "11:30 PM"
.AddItem "11:45 PM"
.AddItem "11:59 PM"
End With
With ComboBox5
.CLEAR
.AddItem "12:00 AM"
.AddItem "12:30 AM"
.AddItem "12:45 AM"
.AddItem "1:00 AM"
.AddItem "1:15 AM"
.AddItem "1:30 AM"
.AddItem "1:45 AM"
.AddItem "2:00 AM"
.AddItem "2:15 AM"
.AddItem "2:30 AM"
.AddItem "2:45 AM"
.AddItem "3:00 AM"
.AddItem "3:15 AM"
.AddItem "3:30 AM"
.AddItem "3:45 AM"
.AddItem "4:00 AM"
.AddItem "4:15 AM"
.AddItem "4:30 AM"
.AddItem "4:45 AM"
.AddItem "5:00 AM"
.AddItem "5:15 AM"
.AddItem "5:30 AM"
.AddItem "5:45 AM"
.AddItem "6:00 AM"
.AddItem "6:15 AM"
.AddItem "6:30 AM"
.AddItem "6:45 AM"
.AddItem "7:00 AM"
.AddItem "7:15 AM"
.AddItem "7:30 AM"
.AddItem "7:45 AM"
.AddItem "8:00 AM"
.AddItem "8:15 AM"
.AddItem "8:30 AM"
.AddItem "8:45 AM"
.AddItem "9:00 AM"
.AddItem "9:15 AM"
.AddItem "9:30 AM"
.AddItem "9:45 AM"
.AddItem "10:00 AM"
.AddItem "10:15 AM"
.AddItem "10:30 AM"
.AddItem "10:45 AM"
.AddItem "11:00 AM"
.AddItem "11:45 AM"
.AddItem "12:00 PM"
.AddItem "12:15 PM"
.AddItem "12:30 PM"
.AddItem "12:45 PM"
.AddItem "1:00 PM"
.AddItem "1:15 PM"
.AddItem "1:30 PM"
.AddItem "1:45 PM"
.AddItem "2:00 PM"
.AddItem "2:15 PM"
.AddItem "2:30 PM"
.AddItem "2:45 PM"
.AddItem "3:00 PM"
.AddItem "3:15 PM"
.AddItem "3:30 PM"
.AddItem "3:45 PM"
.AddItem "4:00 PM"
.AddItem "4:15 PM"
.AddItem "4:30 PM"
.AddItem "4:45 PM"
.AddItem "5:00 PM"
.AddItem "5:15 PM"
.AddItem "5:30 PM"
.AddItem "5:45 PM"
.AddItem "6:00 PM"
.AddItem "6:15 PM"
.AddItem "6:30 PM"
.AddItem "6:45 PM"
.AddItem "7:00 PM"
.AddItem "7:15 PM"
.AddItem "7:30 PM"
.AddItem "7:45 PM"
.AddItem "8:00 PM"
.AddItem "8:15 PM"
.AddItem "8:30 PM"
.AddItem "8:45 PM"
.AddItem "9:00 PM"
.AddItem "9:15 PM"
.AddItem "9:30 PM"
.AddItem "9:45 PM"
.AddItem "10:00 PM"
.AddItem "10:15 PM"
.AddItem "10:30 PM"
.AddItem "10:45 PM"
.AddItem "11:00 PM"
.AddItem "11:15 PM"
.AddItem "11:30 PM"
.AddItem "11:45 PM"
.AddItem "11:59 PM"
End With
With ComboBox6
.CLEAR
.AddItem "12:00 AM"
.AddItem "12:30 AM"
.AddItem "12:45 AM"
.AddItem "1:00 AM"
.AddItem "1:15 AM"
.AddItem "1:30 AM"
.AddItem "1:45 AM"
.AddItem "2:00 AM"
.AddItem "2:15 AM"
.AddItem "2:30 AM"
.AddItem "2:45 AM"
.AddItem "3:00 AM"
.AddItem "3:15 AM"
.AddItem "3:30 AM"
.AddItem "3:45 AM"
.AddItem "4:00 AM"
.AddItem "4:15 AM"
.AddItem "4:30 AM"
.AddItem "4:45 AM"
.AddItem "5:00 AM"
.AddItem "5:15 AM"
.AddItem "5:30 AM"
.AddItem "5:45 AM"
.AddItem "6:00 AM"
.AddItem "6:15 AM"
.AddItem "6:30 AM"
.AddItem "6:45 AM"
.AddItem "7:00 AM"
.AddItem "7:15 AM"
.AddItem "7:30 AM"
.AddItem "7:45 AM"
.AddItem "8:00 AM"
.AddItem "8:15 AM"
.AddItem "8:30 AM"
.AddItem "8:45 AM"
.AddItem "9:00 AM"
.AddItem "9:15 AM"
.AddItem "9:30 AM"
.AddItem "9:45 AM"
.AddItem "10:00 AM"
.AddItem "10:15 AM"
.AddItem "10:30 AM"
.AddItem "10:45 AM"
.AddItem "11:00 AM"
.AddItem "11:45 AM"
.AddItem "12:00 PM"
.AddItem "12:15 PM"
.AddItem "12:30 PM"
.AddItem "12:45 PM"
.AddItem "1:00 PM"
.AddItem "1:15 PM"
.AddItem "1:30 PM"
.AddItem "1:45 PM"
.AddItem "2:00 PM"
.AddItem "2:15 PM"
.AddItem "2:30 PM"
.AddItem "2:45 PM"
.AddItem "3:00 PM"
.AddItem "3:15 PM"
.AddItem "3:30 PM"
.AddItem "3:45 PM"
.AddItem "4:00 PM"
.AddItem "4:15 PM"
.AddItem "4:30 PM"
.AddItem "4:45 PM"
.AddItem "5:00 PM"
.AddItem "5:15 PM"
.AddItem "5:30 PM"
.AddItem "5:45 PM"
.AddItem "6:00 PM"
.AddItem "6:15 PM"
.AddItem "6:30 PM"
.AddItem "6:45 PM"
.AddItem "7:00 PM"
.AddItem "7:15 PM"
.AddItem "7:30 PM"
.AddItem "7:45 PM"
.AddItem "8:00 PM"
.AddItem "8:15 PM"
.AddItem "8:30 PM"
.AddItem "8:45 PM"
.AddItem "9:00 PM"
.AddItem "9:15 PM"
.AddItem "9:30 PM"
.AddItem "9:45 PM"
.AddItem "10:00 PM"
.AddItem "10:15 PM"
.AddItem "10:30 PM"
.AddItem "10:45 PM"
.AddItem "11:00 PM"
.AddItem "11:15 PM"
.AddItem "11:30 PM"
.AddItem "11:45 PM"
.AddItem "11:59 PM"
End With
With ComboBox7
.CLEAR
.AddItem "12:00 AM"
.AddItem "12:30 AM"
.AddItem "12:45 AM"
.AddItem "1:00 AM"
.AddItem "1:15 AM"
.AddItem "1:30 AM"
.AddItem "1:45 AM"
.AddItem "2:00 AM"
.AddItem "2:15 AM"
.AddItem "2:30 AM"
.AddItem "2:45 AM"
.AddItem "3:00 AM"
.AddItem "3:15 AM"
.AddItem "3:30 AM"
.AddItem "3:45 AM"
.AddItem "4:00 AM"
.AddItem "4:15 AM"
.AddItem "4:30 AM"
.AddItem "4:45 AM"
.AddItem "5:00 AM"
.AddItem "5:15 AM"
.AddItem "5:30 AM"
.AddItem "5:45 AM"
.AddItem "6:00 AM"
.AddItem "6:15 AM"
.AddItem "6:30 AM"
.AddItem "6:45 AM"
.AddItem "7:00 AM"
.AddItem "7:15 AM"
.AddItem "7:30 AM"
.AddItem "7:45 AM"
.AddItem "8:00 AM"
.AddItem "8:15 AM"
.AddItem "8:30 AM"
.AddItem "8:45 AM"
.AddItem "9:00 AM"
.AddItem "9:15 AM"
.AddItem "9:30 AM"
.AddItem "9:45 AM"
.AddItem "10:00 AM"
.AddItem "10:15 AM"
.AddItem "10:30 AM"
.AddItem "10:45 AM"
.AddItem "11:00 AM"
.AddItem "11:45 AM"
.AddItem "12:00 PM"
.AddItem "12:15 PM"
.AddItem "12:30 PM"
.AddItem "12:45 PM"
.AddItem "1:00 PM"
.AddItem "1:15 PM"
.AddItem "1:30 PM"
.AddItem "1:45 PM"
.AddItem "2:00 PM"
.AddItem "2:15 PM"
.AddItem "2:30 PM"
.AddItem "2:45 PM"
.AddItem "3:00 PM"
.AddItem "3:15 PM"
.AddItem "3:30 PM"
.AddItem "3:45 PM"
.AddItem "4:00 PM"
.AddItem "4:15 PM"
.AddItem "4:30 PM"
.AddItem "4:45 PM"
.AddItem "5:00 PM"
.AddItem "5:15 PM"
.AddItem "5:30 PM"
.AddItem "5:45 PM"
.AddItem "6:00 PM"
.AddItem "6:15 PM"
.AddItem "6:30 PM"
.AddItem "6:45 PM"
.AddItem "7:00 PM"
.AddItem "7:15 PM"
.AddItem "7:30 PM"
.AddItem "7:45 PM"
.AddItem "8:00 PM"
.AddItem "8:15 PM"
.AddItem "8:30 PM"
.AddItem "8:45 PM"
.AddItem "9:00 PM"
.AddItem "9:15 PM"
.AddItem "9:30 PM"
.AddItem "9:45 PM"
.AddItem "10:00 PM"
.AddItem "10:15 PM"
.AddItem "10:30 PM"
.AddItem "10:45 PM"
.AddItem "11:00 PM"
.AddItem "11:15 PM"
.AddItem "11:30 PM"
.AddItem "11:45 PM"
.AddItem "11:59 PM"

End With
End Sub



Thank you
 

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().
Sort by date Sort by votes
Caribeiro77 said:
But what are the calculations that you wanna do exactly?
Click to expand...

I would like them to calculate the total hours worked less the half hour break.

I have Drive hours calculated on the workbook with the following formula

J6 is the On Site Time
I6 is the Drive Start Time
N6 is the Drive End Time
M6 is the Off Site Time
=SUM((J6-I6)*24)+SUM(N6-M6)*24


Site hours are calculated with the following formula:

M6 is the Off Site Time
J6 is the On Site Time
L6 is the Break End Time
K6 is the Break Start Time

=SUM((M6-J6)*24)-SUM(L6-K6)*24

So the total time worked would be the Drive Start to the Drive End less lunch.

Is it possible to do this same calculation with the Combo boxes.

I have a text box whose value is the total hours. I have tried to add a Command Button that loads the Time Cells but every time I click on it, it clears the user form. I would like for our Technicians to Check their total hours before committing to unloading the entire form. I am not sure what code I should use to prevent resetting the form after it is unloaded. I do want the form reset once the Technician commits to unload the entire form.

Any help would be greatly appreciated.
 
Upvote 0
You must log in or register to reply here.

Similar threads

I
  • Question
Listbox not being populate when code is run
Replies
4
Views
285
ipbr21054
I
A
  • Question
Cannot enter text in textbox on form
Replies
3
Views
130
Anthony47
A
A
  • Solved
Application-defined or object defined Error when Filtering Listbox
Replies
4
Views
140
Peter_SSs
Peter_SSs
0 Agios
  • Question
Inserting formula in range
Replies
2
Views
226
0 Agios
0 Agios
S
  • Question
userform to search and update vba
Replies
0
Views
541
Strycure12
S

Forum statistics

Threads
1,214,834
Messages
6,121,876
Members
449,056
Latest member
ruhulaminappu

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