Userform Controls - Problem suspending textbox change routines

Ark68

Well-known Member
Joined
Mar 23, 2004
Messages
4,562
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
I have run into a problem with my userform control triggers. Consider the code below and this particular scenario.

I have a userform, with two textboxes (cu2_start, cu2_end) in which the user may enter a time.

Here is the control change code for 'cu2_start':
Rich (BB code):
Private Sub cu2_start_BeforeUpdate(ByVal CANCEL As MSForms.ReturnBoolean)
    If mbEvents Then Exit Sub
    On Error GoTo badtime
    
    'mbEvents = True
    bu = cu2_startbu.Value
    stc_cu2 = format(CDate(cu2_start.Value), "0.00000")
    ts1 = usd + stc_cu2
    
    etc_cu2 = stc_cu2 + 8 / 24
    cu2_endbu.Value = etc_cu2
    bu2 = etc_cu2
    ts2 = usd + etc_cu2
    cu2_end.Value = format(ts2, "hh:mm")
    'mbEvents = False
    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_start.SelStart = 0
    cu2_start.Value = format(bu, "hh:mm")
    cu2_start.SelLength = Len(cu2_start.Value)

End Sub

And the 'cu2_end' code ...

Rich (BB code):
Private Sub cu2_end_BeforeUpdate(ByVal CANCEL As MSForms.ReturnBoolean)
    If mbEvents Then Exit Sub
    On Error GoTo badtime
    bu = cu2_endbu.Value 'backup end time
    etc_cu2 = format(CDate(cu2_end.Value), "0.00000")
    ts1 = usd + etc_cu2 'timestamp

    If etc_cu2 = 0 Or etc_cu2 < 0.125 Then 'if time entered is between midnight and 3:00AM then bump up the date
        etc_cu2 = ts1 + 1
    End If
    If etc_cu2 < CDate(cu2_start.Value) 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(bu, "hh:mm")
        Exit Sub
    Else
        stv2 = ts1 'CDate(Me.cu2_start.Value)
        etv2 = ts2 'CDate(Me.cu2_end.Value)
        'jt = IIf(etv2 = 0, 1, etv2)
        Me.cu2_hours.Value = format((etv2 - stv2) / 60, "general number") 'format(DateDiff("n", stv2, jt) / 60, "general number")
        If Me.cu2_hours.Value = 8 Then
            Me.cu2_notes.Value = ""
        ElseIf Me.cu2_hours.Value < 8 Then
            hd = 8 - CDbl(Me.cu2_hours)
            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"
            Else
                cu2_end.Value = format(bu, "h: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, "general number")
                Me.cu2_notes.Value = ""
            End If
        Else        'overtime allocation? If Me.cu3_hours.Value > 8 Then
            hd = CDbl(Me.cu2_hours) - 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(bu, "h: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, "general number")
            End If
        End If
        
    End If
    bu = ""
    'uf9_poststaff.btn_f1_submit.Enabled = True
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(bu, "h:mm")

End Sub

The line "If mbevents then exit sub" disables the event during userform initialization.

Suppose the user enters 16:00 into textbox 'cu2_start'.
Some date calculations are done, including an automatic calculation for the textbox 'cu2_end' value. With 16:00 entered into 'cu2_start', 'cu2_end' value is calculated as 0:00 (cu2_start + 8 hours)
Two fields are updated in the userform as a result of this code ... 'cu2_end' and cu2_endbu'.

When this code runs as it is, the changes in these two fields triggers the event change of textbox 'cu2_end'. This creates havoc with the remainder of the 'cu2_end' change code (which I admit needs tweaking once I can figure this issue out)

I needed to prevent this change code from executing, so I added mbevents = true to the 'cu2_start' trigger code in advance of the update to 'cu2_end' to suspend the event code of 'cu2_end' when it reaches the if mb events then exit sub code. I re-enable the event code with the addition of mbevents = false allowing any further changes to the textboxes to trigger code.

However, with these two lines included in the 'cu2_start' code, nothing changes. The 'cu2_end' change event is triggered the same as it is without these lines. This isn't making a difference.

If I leave the mbevents=true line in the code, and omit mbevents=false, I get the results I'm looking for. BUT! Any further textbox changes don't trigger the change code since mbevents remains true. It has never been put back to false, so the code exits with the if mbevents then exit sub line.

So you can see what I'm running into./ I need to include mbevents = false somewhere in my code, but where I think is a reasonable spot, messes up my code.

I would be most grateful to anyone who feels they can provide some support. If I've failed to provide adequate information, please let me know so I can provide the necessary background to work towards a solution.
 

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.
That should work, it sounds as if you may have other events firing, that turn mbevents to false.
 
Upvote 0

Forum statistics

Threads
1,213,497
Messages
6,113,998
Members
448,539
Latest member
alex78

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top