Overcome A 'BeforeUpdate' trigger when there is nothing to update?

A

Ark68

Well-known Member
Joined
Mar 23, 2004
Messages
4,570
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
I've run into a problem that I hope someone can provide some advice on to resolve.

I have a user form with two textboxes, cu2_start and cu2_end. Normally, these textboxes are filled as part of the userform's initialization procedure. Code associated with a 'BeforeUpdate' trigger of cu2_start will update the value in cu2_end automatically. This change to cu2_end triggers it's 'BeforeUpdate' which all works flawlessly when these two fields had been initially populated.

However, on rare occassion these two fields are empty during initialization. The user enters a value into cu2_start, triggers it's 'BeforeUpdate' code and as a result cu2_end populates. Since cu2_end hadn't a value to update, the 'BeforeUpdate' code fails to trigger, so a good chunk of procedure is missed.
 

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.
Sort by date Sort by votes
I feel one way I can eliminate the problem is to populate empty fields with ain insignificant character and change the forecolor to make it appear empty so that there is a value to update. But, being an afterthought, it will take a lot of effort. I've favouring some method that I may not even be aware of that maybe the expereinced folk know of.
 
Upvote 0
Hi Silentwolf ... unless i did something wrong, I'm not getting any different results with your suggestion. :-(
 
Upvote 0
Hi,
can you post the code of how the initialize event of the userform and the afterupdate event or what you have at present?
 
Upvote 0
Hi Fluff, the only problem with a change event, is in those instances that the user wants to change a value currently popuating cu_end. With the first keystroke, the code is triggered, as opposed to when the user has completely typed their entry.

Here is the code which initially populates the series of "_start" and "_end" textboxes. (the "cu2" was just one of a series that I used as an example).
Code: 
Sub uf9c_ok1()
    
    Dim i
    Dim crew1, mno, ini As String
    Dim arrCtrls
    Dim fnd_row, fnd_trow, ref_row As Integer
    Dim ws_wstofnd As Worksheet
    Dim empno
    
    If uf9_poststaff.mp1_cupe.Value = True Then 'CUPE PAGE
        uf9_poststaff.MultiPage1.Value = 1
    ElseIf uf9_poststaff.mp1_student.Value = True Then 'STUDENT PAGE
        uf9_poststaff.MultiPage1.Value = 2
    ElseIf uf9_poststaff.mp1_wloopk.Value = True Then 'STUDENT PAGE
        uf9_poststaff.MultiPage1.Value = 3
    ElseIf uf9_poststaff.mp1_trim.Value = True Then 'STUDENT PAGE
        uf9_poststaff.MultiPage1.Value = 4
    Else 'MAIN PAGE
        uf9_poststaff.MultiPage1.Value = 0
    End If
    
    With uf9_poststaff
        .MultiPage1.Width = 408
        If uf9_poststaff.mp1_cupe.Value = True Then 'CUPE PAGE
            arrCtrls = Array("CU1", "CU2", "CUA", "CUB", "CUC", "CUD", "CUE", "CUF", "CU7", "CU6", "CU5", "CU4", "CU8", "WPRK1", "WBLVD1")
        ElseIf uf9_poststaff.mp1_student.Value = True Then 'SPORTS PAGE
            arrCtrls = Array("FLD1A", "FLD1B", "FLD2A", "FLD2B", "BPA", "BPB", "BPC", "HPA", "HPB", "HPC", "WPA", "WPB", "WPC", "RPA", "RPB", "RPC")
        ElseIf uf9_poststaff.mp1_wloopk.Value = True Then 'WLOOPK PAGE
            arrCtrls = Array("ZOO1A", "ZOO1B", "ZOO2A", "ZOO2B", "SEA", "SEB", "SEC", "PTA", "PTB", "PTC", "PTD")
        ElseIf uf9_poststaff.mp1_trim.Value = True Then 'TRIM PAGE
            arrCtrls = Array("WPRKA", "WPRKB", "WBLVDA", "WBLVDB")
        End If
        
        For i = LBound(arrCtrls) To UBound(arrCtrls)
            crew1 = arrCtrls(i)
            fnd_row = Application.WorksheetFunction.Match(crew1, ws_rstr.Columns(7), 0)
            empno = ws_rstr.Cells(fnd_row, 1)
            mno = ws_rstr.Cells(fnd_row, 5)
            If empno = "0" Then
                fnd_trow = Application.WorksheetFunction.Match(arrCtrls(i), ws_psfront.Columns(8), 0)
            Else
                fnd_trow = Application.WorksheetFunction.Match(empno, ws_psfront.Columns(5), 0)
            End If
            ini = ws_psfront.Cells(fnd_trow, 10)

            wstofnd = Format(empno, "00000") & "  " & ini
            Set ws_wstofnd = wb_pstaff.Worksheets(wstofnd)
            Debug.Print ws_wstofnd.Name
            
            With ws_wstofnd
                ref_row = Application.WorksheetFunction.Match(CLng(usd), .Columns(2), 0)
            End With
            
            With uf9_poststaff
                .Controls(crew1 & "_en").Caption = Format(empno, "00000")
                .Controls(crew1 & "_name").Value = mno
                .Controls(crew1 & "_start").Value = Format(ws_wstofnd.Cells(ref_row, 3), "h:mm am/pm")
                .Controls(crew1 & "_startbu").Value = Format(ws_wstofnd.Cells(ref_row, 3), "h:mm am/pm")
                .Controls(crew1 & "_end").Value = Format(ws_wstofnd.Cells(ref_row, 4), "h:mm am/pm")
                .Controls(crew1 & "_endbu").Value = Format(ws_wstofnd.Cells(ref_row, 3), "h:mm am/pm")
                .Controls(crew1 & "_hours").Value = Format(ws_wstofnd.Cells(ref_row, 5), "0.00")
                .Controls(crew1 & "_notes").Value = ws_wstofnd.Cells(ref_row, 7)
                .Controls(crew1 & "_notesbu").Value = ws_wstofnd.Cells(ref_row, 7)
            
                If Not IsNumeric(ws_wstofnd.Cells(ref_row, 8)) = True Then
                    .Controls(crew1 & "_en").BackColor = RGB(51, 204, 51)
                    .Controls(crew1 & "_start").Locked = True
                    .Controls(crew1 & "_end").Locked = True
                    .Controls(crew1 & "_notes").Locked = True
                    allgreen = allgreen + 1
                    '.tb_allgreen.Value = allgreen
                    If .MultiPage1.Value = 1 Then
                        cupegreen = cupegreen + 1
                        .tb_cupegreen.Value = cupegreen
                        .tb_allgreen1.Value = allgreen
                    ElseIf .MultiPage1.Value = 2 Then
                        sportsgreen = sportsgreen + 1
                        .tb_sportsgreen.Value = sportsgreen
                        .tb_allgreen2.Value = allgreen
                    ElseIf .MultiPage1.Value = 3 Then
                        wloopkgreen = wloopkgreen + 1
                        .tb_wloopkgreen.Value = wloopkgreen
                        .tb_allgreen3.Value = allgreen
                    ElseIf .MultiPage1.Value = 4 Then
                        trimgreen = trimgreen + 1
                        .tb_trimgreen.Value = trimgreen
                        .tb_allgreen4.Value = allgreen
                    End If
                End If
            End With
        Next i
    End With
    
    With uf9_poststaff
        .Label807.Visible = False
        .Label834.Visible = True
        .todaysdate.Visible = False
        .processdate.Visible = True
        .processdate.Value = Format(usd, "dddd, mmmm dd, yyyy")
    End With
    Unload uf9c_idate
    
    MsgBox "Always make changes to the shift START times (if applicable) before" & Chr(13) & "making changes to the shift END times." & Chr(13) & "To mark an absence, enter the same shift START time" & Chr(13) & "into the shift END time.", vbInformation, "NOTICE"

End Sub

The code when the user changes an existing value or enters a value into a blank "_start" textbox.
Code: 
Private Sub cu2_start_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
    hfg = "cu2"
    c22_start hfg
End Sub

Code: 
Sub c22_start(ByVal hfg As String)

    If Not mbevents Then Exit Sub
    On Error GoTo badtime
    
    mbevents = False
    
    With uf9_poststaff
    
        .Controls(hfg & "_start").Value = Format(.Controls(hfg & "_start").Value, "h:mm am/pm")
        If .Controls(hfg & "_startbu").Value = "" Then
            .Controls(hfg & "_startbu").Value = .Controls(hfg & "_start").Value
        End If
        bu = .Controls(hfg & "_startbu").Value
        stc_cu2 = Format(.Controls(hfg & "_start").Value, "0.00000")
        ts1 = usd + stc_cu2
    
        etc_cu2 = stc_cu2 + 8 / 24
        .Controls(hfg & "_endbu").Value = etc_cu2
        ts2 = usd + etc_cu2
        .Controls(hfg & "_end").Value = Format(ts2, "h:mm am/pm")

        With .MultiPage1

            uf9_poststaff.Controls(hfg & "_start").Locked = True
            uf9_poststaff.Controls(hfg & "_end").Locked = True
            uf9_poststaff.Controls(hfg & "_notes").Locked = True
        End With
    End With
    mbevents = True
    If uf9_poststaff.Controls(hfg & "_end").Value = "" Then
        C22_End hfg
    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
    uf9_poststaff.Controls(hfg & "_start").SelStart = 0
    uf9_poststaff.Controls(hfg & "_start").Value = Format(bu, "h:mm am/pm")
    uf9_poststaff.Controls(hfg & "_start").SelLength = Len(uf9_poststaff.Controls(hfg & "_start").Value)
    mbevents = True

End Sub

The original '_end' (BeforeUpdate") code:
Code: 
Private Sub cu2_end_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
    hfg = "cu2"
    C22_End hfg
End Sub

Code: 
Sub C22_End(ByVal hfg As String)
    
    If Not mbevents Then Exit Sub
    On Error GoTo badtime
    
    stc_cu2 = Format(CDate(uf9_poststaff.Controls(hfg & "_start").Value), "0.00000")
    ts1 = usd + stc_cu2
    
    etc_cu2 = Format(CDate(uf9_poststaff.Controls(hfg & "_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")
    
    'END OF SHIFT TIME BEFORE START TIME
    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
        uf9_poststaff.Controls(hfg & "_end").Value = Format(uf9_poststaff.Controls(hfg & "_endbu"), "h:mm am/pm")
        mbevents = True
        Exit Sub
    
    'END TIME VALID AFTER START TIME
    Else
        stv2 = ts1 'CDate(Me.Controls(hfg & "_start".Value)
        etv2 = ts2 'CDate(Me.Controls(hfg & "_end".Value)
        
        eno2 = CLng(uf9_poststaff.Controls(hfg & "_en").Caption)
        If Application.WorksheetFunction.VLookup(eno2, ws_rstr.Range("A:F"), 6, False) = "STU" Then
            uf9_poststaff.Controls(hfg & "_hours").Value = Format(ahrs - 0.5, "0.00")
            uf9_poststaff.Controls(hfg & "_notes").Value = ""
        Else
            If ahrs = 8 Then
                uf9_poststaff.Controls(hfg & "_hours").Value = Format(ahrs, "0.00")
                uf9_poststaff.Controls(hfg & "_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
                    uf9_poststaff.Controls(hfg & "_notes").Value = "[" & hd & "] hours " & absel & "."
                
                    bcstin = WorksheetFunction.Index(ws_vh.Range("P11:P19"), WorksheetFunction.Match(absel, ws_vh.Range("Q11:Q19"), 0))
                    bcetin = WorksheetFunction.Index(ws_vh.Range("O11:O19"), WorksheetFunction.Match(absel, ws_vh.Range("Q11:Q19"), 0))
                    If bcetin <> "NPY" Then
                        uf9_poststaff.Controls(hfg & "_start").Value = bcstin
                        uf9_poststaff.Controls(hfg & "_start").TextAlign = fmTextAlignLeft
                        uf9_poststaff.Controls(hfg & "_end").Value = bcetin
                        uf9_poststaff.Controls(hfg & "_hours").Value = "8.00"
                    Else
                        uf9_poststaff.Controls(hfg & "_hours").Value = Format(ahrs, "0.00")
                    End If
                    ctv = "OFF"
                Else
                    uf9_poststaff.Controls(hfg & "_end").Value = Format(cu2_endbu.Value, "h:mm am/pm")
                    If ts2 = ts1 Then
                        stv2 = CDate(uf9_poststaff.Controls(hfg & "_start").Value)
                        etv2 = CDate(uf9_poststaff.Controls(hfg & "_end").Value)
                        jt = IIf(etv2 = 0, 1, etv2)
                        uf9_poststaff.Controls(hfg & "_hours").Value = Format(DateDiff("n", stv2, jt) / 60, "0.00")
                    Else
                        uf9_poststaff.Controls(hfg & "_hours").Value = Format((ts2 - ts1) * 24, "0.00")
                    End If
                    uf9_poststaff.Controls(hfg & "_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
                            uf9_poststaff.Controls(hfg & "_notes").Value = "[" & hd & "] hours " & absel & ". [M]"
                        Else
                            uf9_poststaff.Controls(hfg & "_notes").Value = "[" & hd & "] hours " & absel & "."
                        End If
                    Else
                        If hd >= 3 Then
                            uf9_poststaff.Controls(hfg & "_notes").Value = "[" & hd & "] hours " & absel & ". [" & absel2 & "][M]"
                        Else
                            uf9_poststaff.Controls(hfg & "_notes").Value = "[" & hd & "] hours " & absel & ". [" & absel2 & "]"
                        End If
                    End If
                    uf9_poststaff.Controls(hfg & "_end").Value = Format(uf9_poststaff.Controls(hfg & "_end"), "h:mm am/pm")
                    stv2 = CDate(uf9_poststaff.Controls(hfg & "_start").Value)
                    etv2 = CDate(uf9_poststaff.Controls(hfg & "_end").Value)
                    jt = IIf(etv2 = 0, 1, etv2)
                    uf9_poststaff.Controls(hfg & "_hours").Value = Format(DateDiff("n", stv2, jt) / 60, "0.00")
                Else
                    uf9_poststaff.Controls(hfg & "_end").Value = Format(uf9_poststaff.Controls(hfg & "_endbu"), "h:mm am/pm")
                    stv2 = CDate(uf9_poststaff.Controls(hfg & "_start").Value)
                    etv2 = CDate(uf9_poststaff.Controls(hfg & "_end").Value)
                    jt = IIf(etv2 = 0, 1, etv2)
                    uf9_poststaff.Controls(hfg & "_hours").Value = Format(DateDiff("n", stv2, jt) / 60, "0.00")
                End If
            End If
        End If
        
    End If
    
    With uf9_poststaff
        .Controls(hfg & "_en").BackColor = RGB(51, 204, 51)
        allgreen = allgreen + 1
        If allgreen = 46 Then safeexit = True
        With .MultiPage1
            If .Value = 1 Then
                uf9_poststaff.tb_allgreen1.Value = allgreen
                cupegreen = cupegreen + 1
                If cupegreen = 15 Then .uf9_mp1_1_submit.Enabled = True
                uf9_poststaff.tb_cupegreen.Value = cupegreen
            ElseIf .Value = 2 Then
                uf9_poststaff.tb_allgreen2.Value = allgreen
                sportsgreen = sportsgreen + 1
                If sportsgreen = 15 Then .uf9_mp1_2_submit.Enabled = True
                uf9_poststaff.tb_sportsgreen.Value = sportsgreen
            ElseIf .Value = 3 Then
                uf9_poststaff.tb_allgreen3.Value = allgreen
                wloopkgreen = wloopkgreen + 1
                If wloopkgreen = 15 Then .uf9_mp1_3_submit.Enabled = True
                uf9_poststaff.tb_wloopkgreen.Value = wloopkgreen
            ElseIf .Value = 4 Then
                uf9_poststaff.tb_allgreen4.Value = allgreen
                trimgreen = trimgreen + 1
                If trimgreen = 15 Then .uf9_mp1_4_submit.Enabled = True
                uf9_poststaff.tb_trimgreen.Value = trimgreen
            End If
            uf9_poststaff.Controls(hfg & "_start").Locked = True
            uf9_poststaff.Controls(hfg & "_end").Locked = True
            uf9_poststaff.Controls(hfg & "_notes").Locked = True
        End With
        
        
    'update all with submit button rather than individually to maintain original data on EXIT
    'sv = 1 'source value = must update
    'eno = CDbl(cu2_en)
    'cntrls = "cu2"
    
    'submit_post eno, cntrls, ctv
    End With
Exit Sub

badtime:
    MsgBox Err.Number
    errorcap1a = "Invaid time entry. Please retry."
    errorcap1b = "Enter time in 24H format (hh:mm)."
    nt_invalid_time_entry.Show
    Cancel = True
    uf9_poststaff.Controls(hfg & "_end").Value = Format(uf9_poststaff.Controls(hfg & "_endbu").Value, "hh:mm")
    mbevents = True

End Sub
 
Last edited:
Upvote 0
Hi,
so did you try this

Code: 
Private Sub cu2_end_AfterUpdate()
        hfg = "cu2"
    C22_End hfg
End Sub
 
Upvote 0
silentwolf said:
Hi,
so did you try this

Code: 
Private Sub cu2_end_AfterUpdate()
        hfg = "cu2"
    C22_End hfg
End Sub
Click to expand...

Yes, but unfortunately didn't work. See post #4 .
I have a breakpoint at this routine and it is never reached when the value is populated into the field through the cu2_start trigger. Same behaviour as with the BeforeUpdate trigger.
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

P
  • Solved
Advice needed on where to put code that stops duplicate entries
Replies
5
Views
593
Pacman52
P
D
  • Question Question
VBA Basic Userform - add -update -delete
Replies
3
Views
616
Drexi
D
ajjava
  • Question Question
Procedure to find MAX VALUE in a SPECIFIC pivot table value field, in the case of MULTIPLE pivot table value fields
Replies
0
Views
2K
ajjava
ajjava
A
  • Question Question
Issue With Userform Textbox BeforeUpdate Event
Replies
4
Views
3K
Ark68
A
C
  • Question Question
VBA to delete text after multiple carriage return (line break)
Replies
1
Views
1K
Chipperzs
C

Forum statistics

Threads
1,223,911
Messages
6,175,337
Members
452,637
Latest member
Ezio2866

Share this page

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