Macro only calls on the first Before Save, rather than before every save

[SkywardPalm]

I am trying to have this macro run before every save, to ensure that the two totals in question are matching. It only runs the first time I press ctrl+s

ThisWorkbook:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

Call ExtendedTotalCheck

End Sub

DollarTotalCheck:

Sub ExtendedTotalCheck()
    Application.EnableEvents = True
    Dim wsMaster As Worksheet, wsSUMMARY As Worksheet
    Dim extHeaderCell As Range, f As Range
    Dim masterTotal As Double

    Set wsMaster = ThisWorkbook.Sheets("Master")
    Set wsSUMMARY = ThisWorkbook.Sheets("SUMMARY")
  
    Set extHeaderCell = wsMaster.Range("1:1").Find("Extended")
    If Not extHeaderCell Is Nothing Then
            masterTotal = extHeaderCell.Offset(, 1).Value
            Set f = wsSUMMARY.Range("C228:D228").Find(masterTotal, , xlValues, xlWhole)
        If Not f Is Nothing Then
            Debug.Print "Extended Totals Match"
        Else
            MsgBox "Please check Extended Totals match!"
        End If
    End If
End Sub

 

[mumps]

First of all, if you want the code to run before every save, you have to place the code in the Workbook_BeforeSave macro as follows:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    Application.EnableEvents = True
    Dim wsMaster As Worksheet, wsSUMMARY As Worksheet
    Dim extHeaderCell As Range, f As Range
    Dim masterTotal As Double
    Set wsMaster = ThisWorkbook.Sheets("Master")
    Set wsSUMMARY = ThisWorkbook.Sheets("SUMMARY")
    Set extHeaderCell = wsMaster.Range("1:1").Find("Extended")
    If Not extHeaderCell Is Nothing Then
            masterTotal = extHeaderCell.Offset(, 1).Value
            Set f = wsSUMMARY.Range("C228:D228").Find(masterTotal, , xlValues, xlWhole)
        If Not f Is Nothing Then
            Debug.Print "Extended Totals Match"
        Else
            MsgBox "Please check Extended Totals match!"
        End If
    End If
End Sub

The macro runs automatically when you save the file. You don't have to try to run it manually by pressing CTRL+s.

 

[SkywardPalm]

First of all, if you want the code to run before every save, you have to place the code in the Workbook_BeforeSave macro as follows:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    Application.EnableEvents = True
    Dim wsMaster As Worksheet, wsSUMMARY As Worksheet
    Dim extHeaderCell As Range, f As Range
    Dim masterTotal As Double
    Set wsMaster = ThisWorkbook.Sheets("Master")
    Set wsSUMMARY = ThisWorkbook.Sheets("SUMMARY")
    Set extHeaderCell = wsMaster.Range("1:1").Find("Extended")
    If Not extHeaderCell Is Nothing Then
            masterTotal = extHeaderCell.Offset(, 1).Value
            Set f = wsSUMMARY.Range("C228:D228").Find(masterTotal, , xlValues, xlWhole)
        If Not f Is Nothing Then
            Debug.Print "Extended Totals Match"
        Else
            MsgBox "Please check Extended Totals match!"
        End If
    End If
End Sub

The macro runs automatically when you save the file. You don't have to try to run it manually by pressing CTRL+s.

I tried the macro this way, and the pop-up dialog only appears after the first save. Is there a way to make it appears every time the file is saved?

 

[mumps]

The pop up dialog will appear only if the first row in the Master sheet contains the word "Extended".

 

[SkywardPalm]

The pop up dialog will appear only if the first row in the Master sheet contains the word "Extended".

The case should always run because it is always true, however the macro only successfully runs on the first save of a file when it's open. (Nothing changes within the file before the next time the macro runs..) I would the macro to run each time the file is saved without having to close the file in between saves, if that makes since. Thanks for your help

 

[mumps]

If that is the case, then the following line of code would not be needed:

If Not extHeaderCell Is Nothing Then

Would you agree?

 

[SkywardPalm]

If that is the case, then the following line of code would not be needed:

If Not extHeaderCell Is Nothing Then

Would you agree?

I would agree, I guess I was using it as more of a fail-safe in the case that a user deleted the cell.
When I removed that line and the End If, It actually threw a "Run-time error '91': Object variable or With block variable not set" error
I added

MsgBox "hello world"

to the beginning of the Workbook_BeforeSave and it runs on every save as opposed to the macro

 

[SkywardPalm]

I would agree, I guess I was using it as more of a fail-safe in the case that a user deleted the cell.
When I removed that line and the End If, It actually threw a "Run-time error '91': Object variable or With block variable not set" error
I added

MsgBox "hello world"

to the beginning of the Workbook_BeforeSave and it runs on every save as opposed to the macro

It's as if the extHeaderCell stops finding the Extended cell after the first run, and the variable ends up not being set for the "If Not extHeaderCell Is Nothing Then" to run properly.

 

[SkywardPalm]

I've updated the code to this to try and find where the issue is happening.. the debug.print is showing these statement:
Could not find extHeaderCell

I'm not sure why the variable isn't setting properly after the initial save.

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    Dim wsMaster As Worksheet, wsSUMMARY As Worksheet
    Dim extHeaderCell As Range, f As Range
    Dim masterTotal As Double

    Set wsMaster = ThisWorkbook.Sheets("Master")
    Set wsSUMMARY = ThisWorkbook.Sheets("SUMMARY")
    
    Set extHeaderCell = wsMaster.Range("1:1").Find("Extended")
    
    If Not extHeaderCell Is Nothing Then
            masterTotal = extHeaderCell.Offset(, 1).Value
            Set f = wsSUMMARY.Range("C228:D228").Find(masterTotal, , xlValues, xlWhole)
        If Not f Is Nothing Then
            Debug.Print "Extended Totals Match"
        Else
            MsgBox "Please check Extended Totals match!" & vbCrLf & "Master  = $" & masterTotal
        End If
    Else
        Debug.Print "Could not find extHeaderCell"
    End If
    
End Sub

 

[mumps]

Try:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    Application.EnableEvents = True
    Dim wsMaster As Worksheet, wsSUMMARY As Worksheet
    Dim extHeaderCell As Range, f As Range
    Dim masterTotal As Double
    Set wsMaster = ThisWorkbook.Sheets("Master")
    Set wsSUMMARY = ThisWorkbook.Sheets("SUMMARY")
    Set extHeaderCell = wsMaster.Range("1:1").Find("Extended")
    masterTotal = extHeaderCell.Offset(, 1).Value
    Set f = wsSUMMARY.Range("C228:D228").Find(masterTotal, , xlValues, xlWhole)
    If Not f Is Nothing Then
        MsgBox "Extended Totals Match"
    Else
        MsgBox "Please check Extended Totals match!"
        Cancel = True
    End If
End Sub

I've changed the debug.print to msgbox. It also cancels the "save" if "Please check Extended Totals match!" is displayed.