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

Ark68

Well-known Member
Joined
Mar 23, 2004
Messages
3,809
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.
 

Some videos you may like

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.

silentwolf

Well-known Member
Joined
May 14, 2008
Messages
1,134
Office Version
  1. 2016
Hi,
what about try an after update instead would that work for you?

HTH
 

Ark68

Well-known Member
Joined
Mar 23, 2004
Messages
3,809
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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.
 

Ark68

Well-known Member
Joined
Mar 23, 2004
Messages
3,809
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Hi Silentwolf ... unless i did something wrong, I'm not getting any different results with your suggestion. :(
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
52,755
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

How about a change event
 

silentwolf

Well-known Member
Joined
May 14, 2008
Messages
1,134
Office Version
  1. 2016
Hi,
can you post the code of how the initialize event of the userform and the afterupdate event or what you have at present?
 

Ark68

Well-known Member
Joined
Mar 23, 2004
Messages
3,809
Office Version
  1. 365
  2. 2016
Platform
  1. Windows

ADVERTISEMENT

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:

silentwolf

Well-known Member
Joined
May 14, 2008
Messages
1,134
Office Version
  1. 2016
Hi,
so did you try this

Code:
Private Sub cu2_end_AfterUpdate()
        hfg = "cu2"
    C22_End hfg
End Sub
 

Ark68

Well-known Member
Joined
Mar 23, 2004
Messages
3,809
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Disregard
 
Last edited:

Ark68

Well-known Member
Joined
Mar 23, 2004
Messages
3,809
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Hi,
so did you try this

Code:
Private Sub cu2_end_AfterUpdate()
        hfg = "cu2"
    C22_End hfg
End Sub

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:

Watch MrExcel Video

Forum statistics

Threads
1,122,207
Messages
5,594,840
Members
413,944
Latest member
3xc3ln00b

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
Top