I have a form for work that I update and email out daily. I open from one master form ("Passdown Report 3rd Shift"), and save-as individual copies separated by the date ("Passdown Report 3rd Shift 2022-01-19") before I leave each day.

The form is filled with formulas that auto-update based on the day and based off stimuli from the other worksheets in the workbook, so I added a macro to convert all formulas in the range to their values.

I want to run this macro before I save the form as the daily file, but not when I'm simply updating and saving the master file. Is that something I can do?

CodePudding user response：

Run Macro Before Closing a Workbook

• It is expected that you SaveAs as the backup.
• Only when closing the backup, the IF(StrComp... will note a different file name, and your macro will run and the backup will be saved again before closing.
• It is kind of clumsy, but it should ensure the safety of your original.
• The problem with BeforeSave is that you could do If SaveAsUI = True Then but you could also accidentally do the SaveAs on the original and have it 'destroyed'. I consider it too risky.

Option Explicit

Private ClosingBackup As Boolean

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    
    Const sFileName As String = "Passdown Report 3rd Shift.xlsm"
    
    If Not ClosingBackup Then
        With Me
            ' Compare if you're closing the original or the backup.
            If StrComp(.Name, sFileName, vbTextCompare) <> 0 Then ' backup
                MyMacro
                ClosingBackup = True
                ' Here it will again call this sub but will exit because
                ' 'ClosingBackup' is set to True ('If Not ClosingBakup Then').
                .Close SaveChanges:=True
            'Else ' ClosingBackup = False i.e. closing the original; do nothing
            End If
        End With
    'Else ' ClosingBackup = True; closing the backup which was saved; do nothing
    End If

End Sub

CodePudding user response：

You can use the function BeforeSave, on your ThisWorkbook.

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    'Your code
End Sub

In the microsoft documentation is written on the popup of the SaveAs, this code will be executed and the flag SaveAsUI will be true.

https://docs.microsoft.com/en-us/office/vba/api/excel.workbook.beforesave