Force Save As Macro Enabled Workbook Only Problem

[peacefrog23]

First, I want to say thank you to everyone who contributes to this website. I am new to working with macros and VBA – Self taught by reading and using codes found on this site so bear with me if I use any wrong terminology in describing my problem below.

I am running Excel 2016 on a Windows 7 Professional PC - I have created a document saved as a Macro Enabled Template file (.xltm) - When I open the template file it will of course create a new Workbook I am having a problem with forcing the user to save this new file as a macro enabled worksheet (.xlsm) in all types of ways to save a document I can find. I found the following code on this website which I placed in ThisWorkbook under the VBA screen.

If I click File > Save As… it will bring up the browse box and limit my Save As Type to only Macro Enabled Workbook (.xlsm) so it seems to be working.

If I click the Save button icon on the top Quick Access Tool Bar it will also prompt me to Save As and only limit to save as type macro enabled workbook.

The issue I am facing is if a user clicks the X button in the top right corner you will get the pop up saying Want to Save Changes…this is good, I want this to happen. If you click Save it pops up the Save As menu but gives every possible Save As Type…it does not limit to just the Macro Enabled Workbook. My fear is that my users will click save on the default Excel Workbook and not read the next prompt warning about saving like this with macros in the document and how they wont work any more and they will just click yes and now they have just saved a macro-free document and will wonder why the buttons and functionally don’t work right the next time they want to use the spreadsheet.

Another weird thing I have noticed is that if any other excel document is open, along with this new Macro Enabled file I am trying to get saved correctly, that the action of clicking the X and then clicking Save will prompt the Save As and it will have it limited to only saving as type Macro Enabled Workbook.

I have been battling this for the past two days and have not found anything anywhere addressing this same issue – can anyone out there help? Thank you!

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
If ThisWorkbook.Saved = True Then
Exit Sub
Else
If SaveAsUI = True Then
Cancel = True
txtFileName = Application.GetSaveAsFilename(ThisWorkbook.FullName, "Excel Macro-Enabled Workbook (*.xlsm), *.xlsm", , "Save As XLSM file")
If txtFileName = "False" Then
MsgBox "Action Cancelled", vbOKOnly
Cancel = True
Exit Sub
End If
Application.EnableEvents = False
ThisWorkbook.SaveAs Filename:=txtFileName, FileFormat:=52 'xlOpenXMLWorkbookMacroEnabled
Application.EnableEvents = True
End If
End If
End Sub

 

[JLGWhiz]

try adding this to your ThisWorkbook code module and see if it will do what you want.

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    If Right(ThisWorkbook.Name, 4) <> "xlsm" Then
        Dim txtFileName As String
        MsgBox "This workbook must be saved as a macro enabled workbook"
        Cancel = True
        txtFileName = Application.GetSaveAsFilename(ThisWorkbook.FullName, "Excel Macro-Enabled Workbook (*.xlsm), *.xlsm", , "Save As XLSM file")
        If txtFileName = "False" Then
            MsgBox "Action Cancelled", vbOKOnly
            Cancel = True
            Exit Sub
        End If
        Application.EnableEvents = False
            ThisWorkbook.SaveAs Filename:=txtFileName, FileFormat:=52 'xlOpenXMLWorkbookMacroEnabled
        Application.EnableEvents = True
    End If
End Sub

 

[peacefrog23]

Awesome! Thank you for the quick reply! I like this and it does solve the problem by restricting only Macro Enabled Worksheet when clicking the X - my only follow up question, if a user opens the template file and decides they don't want to save the workbook at all, maybe they opened by mistake, is there a way to still let them not save and close? Right now it seems the file must be saved before it can be closed. Thanks again!

 

[JLGWhiz]

This will let you exit if no changes were made to the template.

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    If Right(ThisWorkbook.Name, 4) <> "xlsm" Then
        Dim txtFileName As String, opt As Variant
        opt = MsgBox("If you have made changes to this workbook must be saved as a macro enabled workbook." & vbLf & _
        "If you have made no changes and want to exit without saving, click the 'YES' button", vbYesNo + vbExclamation, "EXIT OPTION")
            If opt = vbYes Then
                Cancel = False
                Exit Sub
            End If
        Cancel = True
        txtFileName = Application.GetSaveAsFilename(ThisWorkbook.FullName, "Excel Macro-Enabled Workbook (*.xlsm), *.xlsm", , "Save As XLSM file")
        If txtFileName = "False" Then
            MsgBox "Action Cancelled", vbOKOnly
            Cancel = True
            Exit Sub
        End If
        Application.EnableEvents = False
            ThisWorkbook.SaveAs Filename:=txtFileName, FileFormat:=52 'xlOpenXMLWorkbookMacroEnabled
        Application.EnableEvents = True
    End If
End Sub

 

[peacefrog23]

This is perfect!!! Thank you so much!!!

 

[JLGWhiz]

You're welcome,
regards, JLG