Calculating Time Units - VBA User Form

shebe228

New Member
Joined
Sep 28, 2017
Messages
46
I have code that will calculate time elapsed and how many 15 minute units are between the two times entered. It was later decided that an user form may be the more appropriate approach for my audience but I have 0 experience with user forms. I made an attempt but I can't figure out how to make the time calculations work correctly. In addition, I need to add some if error statements to restrict the entries to a 12 hour format instead of military.

VBA Code:
Sub CalculateUnits()
  Dim Start As Date
  Dim EndTime As Date
  Dim DateDiff As Single
  Dim Hours As Single
  Dim Units As Single

 ' Get the start time and end time.
  Start = InputBox("Enter the start time" & vbCrLf & "1:00 pm = 13:00" & vbCrLf & "2:00 pm = 14:00" & vbCrLf & "3:00 pm = 15:00" & vbCrLf & "4:00 pm = 16:00" & vbCrLf & "5:00 pm = 17:00" & vbCrLf & "6:00 pm = 18:00" & vbCrLf & "7:00 pm = 19:00" & vbCrLf & "8:00 pm = 20:00" & vbCrLf & "9:00 pm = 21:00" & vbCrLf & "10:00 pm = 22:00" & vbCrLf & "11:00 pm = 23:00" & vbCrLf & "12:00 am = 00:00", "Start Time", "HH:MM")

  EndTime = InputBox("Enter the end time" & vbCrLf & "1:00 pm = 13:00" & vbCrLf & "2:00 pm = 14:00" & vbCrLf & "3:00 pm = 15:00" & vbCrLf & "4:00 pm = 16:00" & vbCrLf & "5:00 pm = 17:00" & vbCrLf & "6:00 pm = 18:00" & vbCrLf & "7:00 pm = 19:00" & vbCrLf & "8:00 pm = 20:00" & vbCrLf & "9:00 pm = 21:00" & vbCrLf & "10:00 pm = 22:00" & vbCrLf & "11:00 pm = 23:00" & vbCrLf & "12:00 am = 00:00", "End Time", "For example:18:00")

 

  ' Calculate the time elapsed
Hours = (EndTime - Start) * 24
  MsgBox ("There are " & Hours & " hour(s) between " & Format(Start, "Medium Time") & " and " & Format(EndTime, "Medium Time"))


  ' Calculate number of 15 minute units
Units = Hours * 4
  MsgBox (Format(Start, "Medium Time") & " and " & Format(EndTime, "Medium Time") & " has " & Units & " billable units ")
End Sub


1575646733295.png

VBA Code:
Private Sub TextBox1_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)

If Not Me.TextBox1 Like "??:??" Then
    MsgBox "Please use format 'hh:mm AM/PM'"
    Cancel = True
    Exit Sub
Else

End If


Me.TextBox1 = Application.WorksheetFunction.Text(Me.TextBox1, "hh:mm AM/PM")

Me.TextBox2 = Application.WorksheetFunction.Text(Me.TextBox2, "hh:mm AM/PM")


End Sub
 

Some videos you may like

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.

Watch MrExcel Video

Forum statistics

Threads
1,123,177
Messages
5,600,160
Members
414,367
Latest member
dw970906

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