Ark68
Well-known Member
- Joined
- Mar 23, 2004
- Messages
- 4,570
- Office Version
- 365
- 2016
- Platform
- Windows
The code below is supposed to do something pretty simple. Take a time value (CDate(Me.tb_ps_sttime.Value))and see if it falls with a shift (defined by a start - ty9s -and end time - ty9e).
The problem I am having is something like this.
CDate(Me.tb_ps_sttime.Value) = 9:00 PM
ty9s = 4:00 PM
ty9e = midnight (0:00 AM the next day)
I need to adapt this code to ensure that it is understood that ty9e is later than ty9s.
Can anyone suggest an adaptation that will change the value of ty9e to midnight the next day when the value of ty9s = "0" (midnight)
Code:
Private Sub cb_ps_crew_Change()
Dim ty9s As Double
Dim ty9e As Double
If mbEvents Then Exit Sub
mbEvents = True
ty9s = WorksheetFunction.VLookup((Me.cb_ps_crew.Value & "1"), ws_staff.Range("I5:M38"), 4, False)
ty9e = WorksheetFunction.VLookup((Me.cb_ps_crew.Value & "1"), ws_staff.Range("I5:M38"), 5, False)
rn = ws_data.Range("A:A").Find(Me.tb_rid.Value).row
If WorksheetFunction.VLookup((Me.cb_ps_crew.Value & "1"), ws_staff.Range("I5:M38"), 2, False) = "X" Then
MsgBox "There is no staff scheduled on this crew." & Chr(13) & "Please select another crew, or adjust the staff schedule.", vbExclamation, "STAFF DEFICIENCY"
Me.cb_ps_crew.Value = ws_data.Range("AU" & rn)
ElseIf CDate(Me.tb_ps_sttime.Value) < ty9s Or CDate(Me.tb_ps_sttime.Value) > ty9e And Me.cb_ps_stq.Value <> "<" Then
MsgBox "This crew is unavailable for this service at the time requested." & Chr(13) & "Please select another crew, adjust the staff schedule or change the service time.", vbExclamation, "STAFF DEFICIENCY"
Me.cb_ps_crew.Value = ws_data.Range("AU" & rn)
End If
cb_ps_crew.BackColor = RGB(255, 255, 255)
mbEvents = False
End Sub
The problem I am having is something like this.
CDate(Me.tb_ps_sttime.Value) = 9:00 PM
ty9s = 4:00 PM
ty9e = midnight (0:00 AM the next day)
I need to adapt this code to ensure that it is understood that ty9e is later than ty9s.
Can anyone suggest an adaptation that will change the value of ty9e to midnight the next day when the value of ty9s = "0" (midnight)