Unwanted Textbox Exit event triggering with userform closing

[Ark68]

I have a userform with a couple textboxes, a 'SUBMIT' button (commandbutton1) which advances the code to the next process after the user enters a value in the textbox, and a 'CANCEL' button (commandbutton2) which cancels the user's ability to enter a value by closing the userform.

I am having a problem. When the 'CANCEL' button is selected, the textbox exit event is triggered with the userform closing.


Userform initialization ...

Private Sub UserForm_Initialize()

    Dim l_rid As Long
    Dim rn As Double

    mbEvents = True

    Set ws_vh = Workbooks("Sports15b.xlsm").Worksheets("VAR_HOLD")
    fn = ws_vh.Range("B3")
    Set ws_core = Workbooks(fn).Worksheets("CORE")

    l_rid = ws_vh.Range("B13")
    rn = ws_core.Range("A:A").Find(l_rid).Row

    If ws_core.Range("DP" & rn) > 0 Then '(1)
        MsgBox "Services already processed"
        With trn_reline
        
'       --- 1 ---
            .tb_r1_srl.Value = ws_core.Range("BK" & rn)
            .tb_r1_sru.Value = ws_core.Range("BL" & rn)
            .cb_r1_division = ws_core.Range("BN" & rn)
            .cb_r1_base.Value = ws_core.Range("BO" & rn)
            .cb_r1_pitch.Value = ws_core.Range("BP" & rn)
            .cb_r1_crew.Value = ws_core.Range("BQ" & rn)
            If ws_core.Range("BM" & rn) > 0 Then
                .ob_r1_reline.Value = True
                .ob_r1_change.Value = False
                .cb_r1_base.Enabled = False
                .cb_r1_pitch.Enabled = False
            Else
                .ob_r1_reline.Value = False
                .ob_r1_change.Value = True
                .cb_r1_base.Enabled = True
                .cb_r1_pitch.Enabled = True
            End If
        
'       --- 2 ---
            .tb_r2_srl.Value = ws_core.Range("BR" & rn)
            .tb_r2_sru.Value = ws_core.Range("BS" & rn)
            .cb_r2_division = ws_core.Range("BU" & rn)
            .cb_r2_base.Value = ws_core.Range("BV" & rn)
            .cb_r2_pitch.Value = ws_core.Range("BW" & rn)
            .cb_r2_crew.Value = ws_core.Range("BX" & rn)
            If ws_core.Range("BT" & rn) > 0 Then
                .ob_r2_reline.Value = True
                .ob_r2_change.Value = False
                .cb_r2_base.Enabled = False
                .cb_r2_pitch.Enabled = False
            Else
                .ob_r2_reline.Value = False
                .ob_r2_change.Value = True
                .cb_r2_base.Enabled = True
                .cb_r2_pitch.Enabled = True
            End If
        End With
    End If '(1)
    mbEvents = True

End Sub

The commandbutton 'CANCEL' code

Private Sub rl_cancel_Click()
    Unload Me
End Sub

The textbox exit code being executed when CANCEL button is selected...

Private Sub tb_r1_sru_Exit(ByVal CANCEL As MSForms.ReturnBoolean)
    If mbEvents Then Exit Sub
    mbEvents = True
    If IsDate(Me.tb_r1_sru.Value) Then
        Me.tb_r1_sru.Value = Format(Me.tb_r1_sru.Value, "h:mm AM/PM")
    Else
        MsgBox "Please enter a valid time (eg. '24:00' or '12:00 PM')"
        Me.tb_r1_sru.Value = ""
        CANCEL = True
    End If
    mbEvents = False
End Sub

 

[Ark68]

I did change the exit event to an afterupdate event and this resolved the problem. I'm not sure if that's the best solution. I hope I don't encounter other issues now with using the afterupdate event.

 

[mikerickson]

This is one of the reasons that everyone recommends using three combo boxes (day month and year) to enter dates rather than a text box.

it looks like you already have global boolean variable to stop cascading events, set it in the QueryClose event so your AfterUpdate won't interfere with closing the workbook.

 

[Ark68]

Thanks Mike ...

This is one of the reasons that everyone recommends using three combo boxes (day month and year) to enter dates rather than a text box.

This is an option in my case where the user is entering a time?

it looks like you already have global boolean variable

Are you referring to "mbEvents"? I get a 'Variable not defined' error despite having been added to my userform initialization module.

set it in the QueryClose event so your AfterUpdate won't interfere with closing the workbook

Not certain I understand what is meant by QueryClose event ...

 

[mikerickson]

You should put this line before any procedure in the user form's code module

Dim mbEvents As Boolean

Yes, you an use two combo boxes to enter hours and minutes rather than dealing with the user's errors entering a text box.

About QueryClose: https://msdn.microsoft.com/en-us/library/office/gg278635.aspx

 

[Ark68]

Hi Mike, again ... thank you.

I wish I had considered the idea of using the multiple combobox concept before I structured 30 textboxes to accept time in my userform. I'm not sure what would be quicker ... resolving the issue I have now (unable to send the cursor back to the originating textbox when the user enters an erronous value. CANCEL=true worked like a charm for the exit event, but lost that bonus with the afterupdate) or adapting the user form with hour and minute comboboxes and togglebuttons for AM & PM. I played around with the concept outside of my project to see how it worked, and I liked what I saw. I did have a challenge though of pulling all three values into one to form a valid time value. I'll need to do a bit more research on that, but I figure that would be the next problem I'd encounter.

As far as the QueryClose event goes, despite my reading a few different sources, I still don't know what it's purpose is in my application. I tried to find some examples of it's use that may be close to my situation to see how it's intended behaviour is supposed to be, but drew a blank. I'm gathering it's a preventative thing, but I found little useful information on where to use it (other than preventing something happening unexpectedly?) let alone to code it to fit my needs. But thank you nonetheless Mike for sharing a new concept with me.

 

[mikerickson]

The user form's Query Close event will fire whenever there is a request to unload the user form. But before the uf actually unloads.
Either a Unload Me line of code or the user pressing the corner X will trigger QueryClose
If you set mbEvents to True in the Query Close event, that will then block all of your event code so your AfterUpdate won't run.

By the way, about converting text boxes to combo boxes., there are a few styles:

Basic
1) Hours combo box has numbers 0 thru 23
2) Minutes combo box has numbers 0 thru 59.

Invervals
1) Hours combo box has numbers 0 thru 23
2) Minutes has limited options, 0, 15, 30, 45 is common

AM/PM checkbox
1)Hours has 0 thru 11
2) Minutes combo box
3) CheckBox (or optiona buttons) for AM/PM


Which do you prefer. This weekend would be a good time for me to write a routine that will replace your text boxes with custom controls (TimeBox).

 

[Ark68]

Oh, OK.
I've added this tidbit to my code. Is this something that should be considered for all userforms Mike? Or just in this particular instance it was of particular importance?

 

[mikerickson]

Many of my user forms have a global boolean to stop events from cascading.

 

[Ark68]

Hi Mike,

This weekend would be a good time for me to write a routine that will replace your text boxes with custom controls (TimeBox).

Thank you for the kind offer. Something I may consider in time as my attempts to figure it out my own fail LOL. For now, I am going to see what I can learn on my own. If I fail, I may take you up on the offer if it still stands.