Avoid Generic Error Trap "On Error GoTo badtime"

[Ark68]

I have a userform with a textbox (cu2_end) in which the user may enter a time. Here is the control change event code for this particular textbox:

Private Sub cu2_end_BeforeUpdate(ByVal CANCEL As MSForms.ReturnBoolean)
    
    If mbEvents Then Exit Sub
    On Error GoTo badtime
    
    stc_cu2 = format(CDate(cu2_start.Value), "0.00000")
    ts1 = usd + stc_cu2
    
    etc_cu2 = format(CDate(cu2_end.Value), "0.00000")
    
    
    If etc_cu2 = 0 Or etc_cu2 < 0.125 Then 'if time entered is between midnight and 3:00AM then bump up the date
        usd = usd + 1
    End If
    ts2 = usd + etc_cu2
    
    ahrs = format((ts2 - ts1) * 24, "0.00")
    
    If ts2 < ts1 Then
        errorcap1a = "Invaid time entry. Please retry."
        errorcap1b = "The end of the shift must be after it's start. [" & format(cu2_start.Value, "h:mm AM/PM") & "]."
        nt_invalid_time_entry.Show
        cu2_end.Value = format(cu2_endbu, "hh:mm")
        Exit Sub
    Else
        stv2 = ts1 'CDate(Me.cu2_start.Value)
        etv2 = ts2 'CDate(Me.cu2_end.Value)

        If ahrs = 8 Then
            Me.cu2_notes.Value = ""
        ElseIf ahrs < 8 Then 'not enough hours
            hd = 8 - ahrs
            uf9dlb1 = "CUPE employee is deficient of min. 8 hours."
            uf9dlb2 = "Please select from below to account for " & hd & " hours."
            uf9d_cupe1.Show
            If absel <> "" Then
                Me.cu2_notes.Value = "[" & hd & "] hours " & absel & "."
                Me.cu2_hours.Value = "8.00"
                mbEvents = True
                cu2_start.Value = WorksheetFunction.index(ws_lists.Range("T37:T43"), WorksheetFunction.index(absel, ws_lists.Range("U37:U43"), 0))
                cu2_end.Value = WorksheetFunction.index(ws_lists.Range("S37:S43"), WorksheetFunction.index(absel, ws_lists.Range("U37:U43"), 0))
                mbEvents = False
            Else
                cu2_end.Value = format(cu2_endbu.Value, "hh:mm")
                If ts2 = ts1 Then
                    stv2 = CDate(Me.cu2_start.Value)
                    etv2 = CDate(Me.cu2_end.Value)
                    jt = IIf(etv2 = 0, 1, etv2)
                    Me.cu2_hours.Value = format(DateDiff("n", stv2, jt) / 60, "0.00")
                Else
                    cu2_hours.Value = format((ts2 - ts1) * 24, "0.00")
                End If
                Me.cu2_notes.Value = ""
            End If
        Else        'overtime allocation? If Me.cu3_hours.Value > 8 Then
            hd = ahrs - 8
            uf9dlb3 = "CUPE employee is elligible for overtime."
            uf9dlb4 = "Please select from below to account for " & hd & " hours."
            uf9d_cupe1ot.Show
            Unload uf9d_cupe1ot
            If absel <> "" Then
                If absel2 = "" Then
                    If hd >= 3 Then
                        Me.cu2_notes.Value = "[" & hd & "] hours " & absel & ". [M]"
                    Else
                        Me.cu2_notes.Value = "[" & hd & "] hours " & absel & "."
                    End If
                Else
                    If hd >= 3 Then
                        Me.cu2_notes.Value = "[" & hd & "] hours " & absel & ". [" & absel2 & "][M]"
                    Else
                        Me.cu2_notes.Value = "[" & hd & "] hours " & absel & ". [" & absel2 & "]"
                    End If
                End If
            Else
                cu2_end.Value = format(cu2_endbu, "hh:mm")
                stv2 = CDate(Me.cu2_start.Value)
                etv2 = CDate(Me.cu2_end.Value)
                jt = IIf(etv2 = 0, 1, etv2)
                Me.cu2_hours.Value = format(DateDiff("n", stv2, jt) / 60, "0.00")
            End If
        End If
        
    End If
    
Exit Sub

badtime:
    errorcap1a = "Invaid time entry. Please retry."
    errorcap1b = "Enter time in 24H format (hh:mm)."
    nt_invalid_time_entry.Show
    CANCEL = True
    cu2_end.Value = format(cu2_endbu.Value, "hh:mm")

End Sub

I've used the line

On Error GoTo badtime

as a generic error trap, trapping anything not remotely close to a time value, like text etc, into the cu2_end texctbox. It works well. (Maybe too well?)

However, there is a point in my code where this textbox is supposed to be automatically populated (through the code in blue) with a value that isn't a time. When it reaches this point, the badtime error handler kicks in and corrects it. I guess it's doing it's job.

Can anyone suggest how I can prevent this form happening when I need to populate this textbox with a non-time value?

 

[Dryver14]

Is there a reason Me.cu2_hours.value can not = 8:00 or 08:00

I'm not sure I understand what the mbEvents is doing.

Not definitive answers just trying to get my head around it

 

[Ark68]

Hi Dryver14, thank you for your inquiry. Forgive me for my inexperience with VBA. My logic needs work, and my terminology and explanations may be ineffective.

Me.cu2_hours is not a time, it's the number of hours between two times defined by textboxes cu2_start and cu2_end. It for the most part will always be equal to 8.00 (not 8:00).
The "generic error trap" protects cu2_start and cu2_end from inappropriate entries, like text.
mbEvents suspends control change events from triggering when control values change. With the code in blue, I don't want the cu2_end control change event to trigger when the value is changed, so I disable it with mbEvents = True (in co-operation with "If mbEvents the exit sub at the top of the event change code).

 

[Ark68]

I had a mistake in these lines that was triggering the error handler. Index was supposed to be Match

cu2_start.Value = WorksheetFunction.index(ws_lists.Range("T37:T43"), WorksheetFunction.index(absel, ws_lists.Range("U37:U43"), 0))
cu2_end.Value = WorksheetFunction.index(ws_lists.Range("S37:S43"), WorksheetFunction.index(absel, ws_lists.Range("U37:U43"), 0))

Once fixed, nothing to trigger the handler so all worked as hoped.