Calculating Time Units - VBA User Form

[shebe228]

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.

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

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