My UserForm Closes With Exit Sub Command ... I think I'm Doing Something Wrong ... Looking For The Alternative

[Ark68]

I have a textbox (cupe1_start) in my userform (uf3_create_wo2) that the user is to enter a time value. Once the user has made an entry, code is used to check the appropriateness of the entry.

Private Sub cupe1_start_BeforeUpdate(ByVal CANCEL As MSForms.ReturnBoolean)
    Call CheckEntry(cupe1_start, CANCEL) 'module11
    If gflag <> 1 Then Exit Sub 'if time entry (module 11} if invalid
    Me.cupe1_start = Format(Me.cupe1_start.Value, "h:mm AM/PM")
    Me.cupe1_startb = Format(Me.cupe1_start.Value, "h:mm AM/PM")
    Me.cupe1_end = Format(DateAdd("H", 8, Me.cupe1_start.Value), "hh:mm")
    Me.cupe1_endb = Format(Me.cupe1_end.Value, "h:mm AM/PM")
    With ws_staff
        .Range("U25") = Format(Me.cupe1_start.Value, "General Number")
        .Range("V25") = Format(Me.cupe1_end.Value, "General Number")
    End With
End Sub

The beforeupdate code of the textbox entry executes procedure CheckEntry to check the enty's appropriateness.

Sub CheckEntry(aTextBox As MSForms.TextBox, ByVal CANCEL As MSForms.ReturnBoolean)
    
    Dim crew1 As String
    Dim t As Date
    Dim min_time_limit As Date
    Dim max_time_limit  As Date
    Dim sTime As String
    
    min_time_limit = TimeValue("5:30 AM")
    max_time_limit = TimeValue("5:00 PM")
    
    With aTextBox
                If Len(aTextBox) < 1 Then
            sTime = ""
        Else
            sTime = GetTime(.Text)
        End If
        If sTime = "" Then 'invalid
            errorcap1a = "Invaid time entry. Please retry."
            errorcap1b = "Enter time in 24H format (hh:mm)."
            nt_invalid_time_entry.Show
            CANCEL = True
            .Value = ""
            .BackColor = RGB(220, 20, 60)
            .SelStart = 0
            .SelLength = Len(.Text)
            gflag = 0
            Exit Sub
        Else
            .BackColor = RGB(255, 255, 255)
        End If
        
        .Text = Format(sTime, "h:mm AM/PM")
        
    End With
    gflag = 1 'time entry valid
    
End Sub

If the user's entry is inappropriate a message is displayed (userform nt_invalid_time_entry) and closed upon user acknolwedgement, and the form field is cleared and highlighted before exiting the CheckEntry routine.

This returns to cupe1_start_BeforeUpdate event code. Since gflag <> 1 (gflag = 0), the sub exits. (line in red). But, it also closes the userform uf3_create_wo2 which I don't want to happen. I want it left open so the user can correct the invalid entry in the textbox just assessed.

Am I taking the wrong approach? Is Exit Sub not the correct way to return to the userform? I don't wish to re-initialize the userform if I don't have to.

 

[Ark68]

Anyone? Am I lacking information that might help?

 

[Ark68]

OK, I'm providing some more code that I failed to include in my OP. I think "a message is displayed (userform nt_invalid_time_entry) and closed" is the culprit. The userform based message is called in CheckEntry routine.

Private Sub UserForm_Initialize()
    label566a.Caption = errorcap1a
    label566b.Caption = errorcap1b
End Sub

The initialization code simply populates the two textboxes with caption values determined from CheckEntry procedure.

Private Sub Image1_Click()
    Unload nt_invalid_time_entry
End Sub

Private Sub label566a_Click()
    Unload nt_invalid_time_entry
End Sub

Private Sub label566b_Click()
    Unload nt_invalid_time_entry
End Sub

Private Sub UserForm_Click()
    Unload nt_invalid_time_entry
End Sub

To close the form and return to uf3_create_wo2, the user can click in any portion of the userform as per the 4 possible click points in the form.

Private Sub UserForm_QueryClose(CANCEL As Integer, CloseMode As Integer)
     
    If CloseMode = 0 Then
        CANCEL = True
        'MsgBox "You can't touch this!", vbCritical
    End If
     
End Sub

This code prevents the user from closing the userform with the upper right "[X]"

If I bypass the message userform by skipping

nt_invalid_time_entry.Show

, everything works as planned. uf3_create_wo2 doesn't close and the user can resume using it.

Something about the use of userform nt_invalid_time_entry is causing uf3_create_wo2 to close prematurely.

Does this help?

 

[alansidman]

crossposted: Userform Prematurely Closing

 

[Ark68]

Setting nt_invalid_time_entry.ShowModal = False seems to have solved the problem. (?)