Unable to Stop BeforeUpdate routine from triggering inappropriately.

[Ark68]

Consider this scenario ...

A textbox on a userform (cupe_1_end) is automatically populated with a time value during the userform's initialization. The user is permitted to change this value. Once the user tabs or clicks out of this textbox after entering a value, a BeforeUpdate event triggers and executing a routine to check the appropriateness of the entry (time value in hh:mm format).

Private Sub cupe1_end_BeforeUpdate(ByVal CANCEL As MSForms.ReturnBoolean)
    Dim dc2 As Date
    dc2 = TimeValue(Me.cupe1_start)
    
    Call CheckEntry2(cupe1_end, CANCEL, dc2) 'module11
    If gflag <> 1 Then Exit Sub 'if time entry (module 11} if invalid
    Me.cupe1_end = Format(Me.cupe1_end.Value, "h:mm AM/PM")
    Me.cupe1_endb = Format(Me.cupe1_end.Value, "h:mm AM/PM")
    With ws_staff
        .Range("L7") = Format(Me.cupe1_start.Value, "General Number")
        .Range("M7") = Format(Me.cupe1_end.Value, "General Number")
    End With
End Sub

In my testing the user entered 12:00 into textbox and dc2 = 1:00:00 PM".

ub CheckEntry2(aTextBox As MSForms.TextBox, ByVal CANCEL As MSForms.ReturnBoolean, ByVal dc2 As Date)
    
    Dim crew1 As String
    Dim t As Date
    Dim sTime As String
    
    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(0, 126, 167)
            .TabIndex = 0
            .SetFocus
            gflag = 0
            Exit Sub
        
        Else

            If Not (sTime >= dc2) Then
                errorcap1a = "Invaid time entry. Please retry."
                errorcap1b = "Enter a time greater than " & Format(dc2, "h:mm AM/PM")
                nt_invalid_time_entry.Show
                CANCEL = True
                .Value = Format(DateAdd("H", 8, dc2), "h:mm AM/PM")
                Exit Sub
            End If
        End If
        .Text = Format(sTime, "h:mm AM/PM")
       
    End With
    gflag = 1 'no problem with time entry

End Sub

The CheckEntry2 code first checks to see if anything was entered, not a null value. If its null variable sTime = "" and if there was an entry, a custom function GetTime is launched. GetTime will determine if the time is a valid entry (eg no alpha characters). An entry deemed valid in GetTime is assigned to variable stime.

Function GetTime(ByVal sTime As String) As Variant
    Dim vparts
    Dim ap As String
    vparts = VBA.Split(sTime, ":")
    If UBound(vparts) < 1 Then
        sTime = vparts(0) & ":00"
    Else
        If Len(vparts(1)) <> 2 Then vparts(1) = VBA.Left$(vparts(1) & "00", 2)
    End If
    sTime = VBA.Join(vparts, ":")

    
    
    If IsDate(sTime) Then
        GetTime = TimeValue(sTime)
    Else
        GetTime = ""
    End If
End Function

In our example, sTime = 12:00:00 PM.

CheckEntry2 then checks to see if the value has been deemed invalid in GetTime. If it is, userform nt_invalid_time_entry is displayed advising the user of the correction needing to be made. Clicking on that userform returns the user to the original userform to reenter the value in the now blanked out textbox.

CheckEntry2 checks to see then whether the entered time is less than or equal to dc2. It our case, sTime (12:00:00PM) is less than dc2 (1:00:00 PM). Userform nt_invalid_time_entry is displayed advising the user of the correction needing to be made. Clicking on that userform returns the user to the original userform to reenter the value. The cupe1_end textbox has been repopulated to a value 8 hours greater than dc2. In this case, textbox cupe_1_end is populated with a value = 9:00:00 PM.

This all works wonderfully. The problem is, now whenever I click another control in the userform Private Sub cupe1_end_BeforeUpdate insists on executing even though the value hasn't been changed by the user. Userform nt_invalid_time_entry is displayed, the user clicks out, the value for cupe1_end is updated (to the same value that had been there since no one changed it), and the cycle continues regardless of the control I click on.

Thank you for reading all this, but I felt it important that I provide as much info as possible. Is it obvious what the issue is here? What must I do to stop the inappropriate triggering of Sub cupe1_end_BeforeUpdate?

Thank you all in advance. All supoort is greatly appreciated.

 

[Ark68]

I guess it's not that obvious ...

I tried changing the cupe1_end event ...

AfterUpdate resulted in an error - "Procedure declaration does not match description of event or procedure having the same name." - Guess that's not going to work.
Exit - does the same as Before Update

I openly admit to knowing little about VBA, so still holding hope that someone is able to identify the flaw. I don't understand enough to know how or where things could go wrong.

 

[Ark68]

Crossposted Here