Ark68
Well-known Member
- Joined
- Mar 23, 2004
- Messages
- 4,570
- Office Version
- 365
- 2016
- Platform
- Windows
Hi folks, I have to admit I'm really struggling with how to work with userform control events. I thought I had it, but it doesn't matter how I seem to do it, there are some undesirable consequences.
Consider a simple userform with two textboxes (tb_s1_lwr and tb_s1_upr). Both are to accept the users entry of a time value (in 24 hour format.)
Until a valid time is entered into tb_s1_lwr, the second textbox is disabled. This ensures that the user puts a value in the first box first.
tb_s1_lwr has an exit event associated to it, which does the error checking (time appropriateness) and then sets up the second box to accept input (clears value, shades background and the tb_s1_upr.setfocus is supposed to send the cursor to that textbox to accept input).
tb_s1_upr works similarly. The user enters a time value, but I have opted for a beforeupdate event to check the appropriateness of the entry (has to be a time value, has to be greater than the time value in tb_s1_lwr, and has to fall within the booking (bkg) range). If the entry is inappropriate, the code will default that textbox to it's original state (empty value, shaded background, and a blinking cursor to await reentry which isn't happening). The user can click in the box and enter times to their hearts content until one passes and unlocks the next field for user entry.
The issue I am having is this. If the user errs in putting in time in tb_s1_upr, they may choose to change the time in tb_s1_lwr. So they click in that textbox (tb_s1_lwr) to make that edit. But as soon as they click in that box, it triggers the BeforeUpdate event of tb_s1_upr, and displays the message that a valid time entry needs to be provided. This is just an annoyance, it doesn't restrict the user from changing anything in tb_s1_lwr. I am not confident to say that the way I'm approaching this is the best, that's why I'm here looking to the pros.
Now, I do have an "enter" event associated to tb_s1_lwr. It was put in when I was experimenting with an tb_s1_upr.exit event (which had it's own undesireable results), so it may be redundant. It's purpose was to reset tb_s1_upr if the user went back to change the value in tb_s1_lwr. Basically, I do not want any value in tb_s1_upr while there is no committed value in tb_s1_lwr.
Consider a simple userform with two textboxes (tb_s1_lwr and tb_s1_upr). Both are to accept the users entry of a time value (in 24 hour format.)
Until a valid time is entered into tb_s1_lwr, the second textbox is disabled. This ensures that the user puts a value in the first box first.
tb_s1_lwr has an exit event associated to it, which does the error checking (time appropriateness) and then sets up the second box to accept input (clears value, shades background and the tb_s1_upr.setfocus is supposed to send the cursor to that textbox to accept input).
tb_s1_upr works similarly. The user enters a time value, but I have opted for a beforeupdate event to check the appropriateness of the entry (has to be a time value, has to be greater than the time value in tb_s1_lwr, and has to fall within the booking (bkg) range). If the entry is inappropriate, the code will default that textbox to it's original state (empty value, shaded background, and a blinking cursor to await reentry which isn't happening). The user can click in the box and enter times to their hearts content until one passes and unlocks the next field for user entry.
The issue I am having is this. If the user errs in putting in time in tb_s1_upr, they may choose to change the time in tb_s1_lwr. So they click in that textbox (tb_s1_lwr) to make that edit. But as soon as they click in that box, it triggers the BeforeUpdate event of tb_s1_upr, and displays the message that a valid time entry needs to be provided. This is just an annoyance, it doesn't restrict the user from changing anything in tb_s1_lwr. I am not confident to say that the way I'm approaching this is the best, that's why I'm here looking to the pros.
Now, I do have an "enter" event associated to tb_s1_lwr. It was put in when I was experimenting with an tb_s1_upr.exit event (which had it's own undesireable results), so it may be redundant. It's purpose was to reset tb_s1_upr if the user went back to change the value in tb_s1_lwr. Basically, I do not want any value in tb_s1_upr while there is no committed value in tb_s1_lwr.
Code:
Private Sub tb_s1_lwr_Enter()
'Stop
If Not mbevents Then Exit Sub
mbevents = False
Me.tb_s1_upr.Value = ""
mbevents = True
Me.tb_s1_upr.BackColor = vbWhite
Me.tb_s1_upr.Enabled = False
Me.Label5.Enabled = False
End Sub
Private Sub tb_s1_upr_beforeupdate(ByVal Cancel As MSForms.ReturnBoolean)
Stop
If Not mbevents Then Exit Sub
mbevents = False
If IsDate(Me.tb_s1_upr.Value) Then
tb_s1_upr.Value = Format(Me.tb_s1_upr.Value, "H:MMA/P")
tb_s1_upr.BackColor = RGB(255, 255, 255) 'white
svc_end = TimeValue(Me.tb_s1_upr.Value)
supr_time = bkg_date + svc_end
'is time within the booking
If supr_time <= bkg_dst Or supr_time >= bkg_det Then
MsgBox "The service time entered is outside the booking time.", vbExclamation, "INVALID TIME ENTRY"
Me.tb_s1_upr.Value = ""
Me.tb_s1_upr.BackColor = RGB(206, 234, 232)
Cancel = True
mbevents = True
Exit Sub
End If
'is date after the lower range time
svc_start = TimeValue(Me.tb_s1_lwr.Value)
slwr_time = CDbl(bkg_date + svc_start)
If CDbl(supr_time) < slwr_time Then
MsgBox "The service time entered has to be equal (no range) to or later that the lower range time.", vbExclamation, "INVALID TIME ENTRY"
Me.tb_s1_upr.Value = ""
Me.tb_s1_upr.BackColor = RGB(206, 234, 232)
Cancel = True
mbevents = True
Exit Sub
End If
cb_s1_crew.Enabled = True
cb_s1_crew.BackColor = RGB(206, 234, 232)
cb_s1_crew.SetFocus
Label6.Enabled = True
Else
MsgBox "Please enter time as h:mm using 24 hour clock.", vbExclamation, "INVALID TIME ENTRY"
Me.tb_s1_upr.Value = ""
tb_s1_upr.BackColor = RGB(206, 234, 232)
Cancel = True
mbevents = True
Exit Sub
End If
mbevents = True
End Sub
Private Sub tb_s1_lwr_Exit(ByVal Cancel As MSForms.ReturnBoolean)
'Stop
Dim svc_start As Double
If Not mbevents Then Exit Sub
mbevents = False
If IsDate(Me.tb_s1_lwr.Value) Then
tb_s1_lwr.Value = Format(Me.tb_s1_lwr.Value, "H:MMA/P")
tb_s1_lwr.BackColor = RGB(255, 255, 255) 'white
'is time within booking range
svc_start = TimeValue(Me.tb_s1_lwr.Value)
slwr_time = bkg_date + svc_start
If slwr_time <= bkg_dst Or slwr_time >= bkg_det Then
MsgBox "The service time entered is outside the booking time.", vbExclamation, "INVALID TIME ENTRY"
Me.tb_s1_lwr.Value = ""
Me.tb_s1_lwr.BackColor = RGB(206, 234, 232)
Cancel = True 'Me.tb_s1_lwr.SetFocus
mbevents = True
Exit Sub
End If
Else
MsgBox "Please enter time as h:mm using 24 hour clock.", vbExclamation, "INVALID TIME ENTRY"
Me.tb_s1_lwr.Value = ""
tb_s1_lwr.BackColor = RGB(206, 234, 232)
Cancel = True 'Me.tb_s1_lwr.SetFocus
mbevents = True
Exit Sub
End If
tb_s1_upr.Enabled = True
tb_s1_upr.BackColor = RGB(206, 234, 232)
Label5.Enabled = True
Cancel = False
tb_s1_upr.SetFocus
mbevents = True
End Sub