Ark68
Well-known Member
- Joined
- Mar 23, 2004
- Messages
- 4,564
- Office Version
- 365
- 2016
- Platform
- Windows
I've run into a snag in my Excel VBA project.
In my userform (userform1), I am using a combination of 4 combo boxes (uf1cb_hrstart, uf1cb_minstart, uf1cb_hrend, and uf1cb_minend) to allow the user to select hr and minute values for both start and end times.
Upon userform1 initialization these 4 boxes are empty and with the exception of uf1cb_hrstart, are disabled disabling the user from entering any information before the start hour is entered.
When the user selects the start hour (0-23) from the drop down, uf1cb_minstart is enabled and the minute value (uf1cb_minstart) is populated with "00" with an control change event. The changing of the start minute value the enables the remaining two comboboxes for the end hour and end minute values. The end hour and minutes (uf1cb_hrend, uf1cb_minend) are automatically populated to (start hours +8), and "00" respectively. All is great.
However, I run into a problem when the user changes the start time by selecting a different start hour once initially set. I am unable to trigger the code uf1cb_minstart change code. The only way it will execute, is if the user changes changes the value in the uf1cb_minstart control. I'm assuming I can't trigger the change event with code?
Is anyone able to provide a solution for what I am looking to do? Many thanks in advance.
PS ... you might also see my failed attempts of error catching if the user enters an end time before the start time. I've discovered that this doesn't work for shifts that extend into the early hours of the next day. If anyone wants to take a loon at this too, I will be eventually posting it as it's issue once I get the initial one figured out.
In my userform (userform1), I am using a combination of 4 combo boxes (uf1cb_hrstart, uf1cb_minstart, uf1cb_hrend, and uf1cb_minend) to allow the user to select hr and minute values for both start and end times.
Upon userform1 initialization these 4 boxes are empty and with the exception of uf1cb_hrstart, are disabled disabling the user from entering any information before the start hour is entered.
When the user selects the start hour (0-23) from the drop down, uf1cb_minstart is enabled and the minute value (uf1cb_minstart) is populated with "00" with an control change event. The changing of the start minute value the enables the remaining two comboboxes for the end hour and end minute values. The end hour and minutes (uf1cb_hrend, uf1cb_minend) are automatically populated to (start hours +8), and "00" respectively. All is great.
However, I run into a problem when the user changes the start time by selecting a different start hour once initially set. I am unable to trigger the code uf1cb_minstart change code. The only way it will execute, is if the user changes changes the value in the uf1cb_minstart control. I'm assuming I can't trigger the change event with code?
Rich (BB code):
Private Sub uf1cb_hrstart_Change()
'Stop
uf1cb_minstart.Enabled = True
uf1cb_minstart.Value = "00" 'this line will not trigger uf1cb_minstart after subsequent changes to uf1cb_hrstart. Only triggers uf1cb_minstart code after the hour is first set[
End Sub
Private Sub uf1cb_minstart_Change()
If Not mbEvents Then Exit Sub
Dim temp_end As Long
uf1cb_hrend.Enabled = True
temp_end = uf1cb_hrstart + 8
If temp_end > 24 Then
n_date = n_date + 1
uf1_ndate = Format(n_date, "dd-mm-yy")
uf1cb_hrend = 8 - (24 - uf1cb_hrstart.Value)
Else
uf1cb_hrend = uf1cb_hrstart + 8
End If
mbEvents = False
uf1cb_minend = uf1cb_minstart.Value
mbEvents = True
uf1cbx2_eqt.SetFocus
End Sub
Private Sub uf1cb_hrend_Change()
If Not mbEvents Then Exit Sub
uf1cb_hrend.BackColor = RGB(244, 247, 252)
uf1cb_minend.Enabled = True
uf1cb_minend.ForeColor = black
End Sub
Private Sub uf1cb_minend_Change()
If Not mbEvents Then Exit Sub
'Stop
start_time = n_date + ((uf1cb_hrstart + (uf1cb_minstart / 60)) / 24)
'MsgBox start_time
'MsgBox Format(start_time, "mmm-dd-yyyy hh:mm")
'If UserForm1.uf1cb_hrend.Value = 24 Then n_date = n_date + 1
end_time = n_date + ((uf1cb_hrend + (uf1cb_minend / 60)) / 24)
'MsgBox end_time
'MsgBox Format(end_time, "mmm-dd-yyyy hh:mm")
If end_time < start_time Then
ui1 = MsgBox("Does this shift extend into the next day (" & Format(n_date + 1, "dd-mmm-yy"), vbYesNo + vbExclamation, "INQUIRY")
If vbYesNo = vbYes Then
uf1_ndate = n_date + 1
Else
mbEvents = False
errmsg1 = "Error: Shift End has to be later than shift start."
errtitle = "ERROR : INVALID SHIFT TIME"
uf1cb_hrend.Value = ""
uf1cb_hrend.BackColor = RGB(255, 163, 163)
uf1cb_minend.Value = ""
uf1cb_minend.BackColor = RGB(255, 163, 163)
uf1cb_minend.Enabled = False
uf1cb_hrend.SetFocus
mbEvents = True
err_box.Show
End If
ElseIf end_time = start_time Then
mbEvents = False
errmsg1 = "Error: Shift End is the same as shift start."
errtitle = "ERROR : INVALID SHIFT TIME"
uf1cb_hrend.Value = ""
uf1cb_hrend.BackColor = RGB(255, 163, 163) 'error red
uf1cb_minend.Value = ""
uf1cb_minend.BackColor = RGB(255, 163, 163)
uf1cb_minend.Enabled = False
uf1cb_hrend.SetFocus
mbEvents = True
err_box.Show
Else
uf1cb_hrend.BackColor = RGB(244, 247, 252)
uf1cb_minend.BackColor = RGB(244, 247, 252)
uf1cbx2_eqt.SetFocus
End If
End Sub
Is anyone able to provide a solution for what I am looking to do? Many thanks in advance.
PS ... you might also see my failed attempts of error catching if the user enters an end time before the start time. I've discovered that this doesn't work for shifts that extend into the early hours of the next day. If anyone wants to take a loon at this too, I will be eventually posting it as it's issue once I get the initial one figured out.
Last edited: