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
 

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.

Forum statistics

Threads
1,213,489
Messages
6,113,947
Members
448,534
Latest member
benefuexx

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