Issue With Userform Textbox BeforeUpdate Event

[Ark68]

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.

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

 

[RoryA]

To be honest, I've never been a fan of this approach. I always prefer to leave validation messages to the code that actually submits the data to whatever storage you are using. It's fine to highlight invalid data as it's entered in a control, but having messages pop-up is annoying for precisely the reason you mention. I think it's better to let them enter all the info and edit it in whatever order they like, then only validate it verbosely when they click Submit/Save/whatever your button is called.

 

[Ark68]

I see Rory. Thanks for sharing that approach! I'll give it a go.
But, would I use the same logic if the information being entered was using in real time calculations as opposed to something that is saved? If that makes sense?

 

[RoryA]

I'd still just highlight them (eg Red for in error). You could always have your result control display some sort of error indicator instead of its result - eg "Invalid date entry"

 

[Ark68]

Very nice. Thanks.