Reminder when Saved

[Raychill Canuck]

So, I have some code (see below) that won't allow users to save the spreadsheet until certain cells are filled in. I now want to include code that will remind the user to save as macro enabled spreadsheet (xlsm), but only if the file isn't already xlsm.

Here's my code so far:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
If Application.CountA(Range("P19, P29, P31")) <> _
    Range("P19, P29, P31").Count Then
    MsgBox "Time To Reoccupation, Quote Done By and Job Type must be filled in before saving quote"
    Cancel = True
End If
End Sub

 

[Joe4]

If you are putting this VBA code in the workbook, aren't you already saving it as an "xlsm" (so by the time the users get it, it is already an "xlsm")?
How would it be anything other than an "xlsm" by the time your users get it?

 

[Raychill Canuck]

Sorry, forgot to mention, it's a template when the user starts with the file. When you select save, it defaults them to xls.

 

[Joe4]

Have a look here: https://answers.microsoft.com/en-us...o/04e3cc9d-e26c-4988-a33b-fcf31f723e8e?auth=1

 

[Raychill Canuck]

So I would add the code in the link after my existing code?

Thanks for all you help. I have a file all set up to test this.

 

[Raychill Canuck]

So I tested the code suggested in the link, and it works.

However I can't figure out how to combine it with my original code so that the user can't save until the required fields are filled in. Any suggestions?

 

[Joe4]

Just try placing the block of code under the other (in the same procedure).

 

[Raychill Canuck]

So I tried this. When I go to save it brings up the error message that the fields are not filled in, but instead of stopping the save until the fields are filled out, it proceeds with the save (as xlsm).

My code is as follows:

Option Explicit

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
 If Application.CountA(Range("P19, P29, P31")) <> _
    Range("P19, P29, P31").Count Then
    MsgBox "Time To Reoccupation, Quote Done By and Job Type must be filled in before saving quote"
    Cancel = True
End If
Dim fname As Variant
    On Error GoTo ErrorHandler
    If SaveAsUI Then
        Cancel = True   'Cancel the original SaveAs
         'Get filename (with path) for saving
        fname = Application.GetSaveAsFilename(fileFilter:="Excel Marcro-Enabled Workbook (*.xlsm),*.xlsm")
        If fname = False Then Exit Sub  'Exit if user hit Cancel
        Application.EnableEvents = False  'Prevent this event from firing
        ThisWorkbook.SaveAs Filename:=fname, FileFormat:=52
          '52 = xlOpenXMLWorkbookMacroEnabled = xlsm (with macro's in 2007-2010)
        Application.EnableEvents = True  'Re-enable events
    End If
Exit Sub
ErrorHandler:
    Application.EnableEvents = True   'So events are never left disabled.
    MsgBox "An error occured during save." & Err.Number, vbCritical, "Error"

End Sub

 

[Joe4]

OK. That is because the second part is running no matter what happens in the first part. So I think what we want to do is change the first "END IF" to an "ELSE", and add the "END IF" to the end, like this:

Option Explicit


Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
 
If Application.CountA(Range("P19, P29, P31")) <> _
    Range("P19, P29, P31").Count Then
    MsgBox "Time To Reoccupation, Quote Done By and Job Type must be filled in before saving quote"
    Cancel = True
Else
    Dim fname As Variant
    On Error GoTo ErrorHandler
    If SaveAsUI Then
        Cancel = True   'Cancel the original SaveAs
         'Get filename (with path) for saving
        fname = Application.GetSaveAsFilename(fileFilter:="Excel Marcro-Enabled Workbook (*.xlsm),*.xlsm")
        If fname = False Then Exit Sub  'Exit if user hit Cancel
        Application.EnableEvents = False  'Prevent this event from firing
        ThisWorkbook.SaveAs Filename:=fname, FileFormat:=52
          '52 = xlOpenXMLWorkbookMacroEnabled = xlsm (with macro's in 2007-2010)
        Application.EnableEvents = True  'Re-enable events
    End If
End If

Exit Sub
ErrorHandler:
    Application.EnableEvents = True   'So events are never left disabled.
    MsgBox "An error occured during save." & Err.Number, vbCritical, "Error"

End Sub

 

[Raychill Canuck]

Thank you very much. This clears the last hurdle in order for me to present this to the estimating team.