Force save Excel-file as Macro-enabled

Sometimes when a Macro-enabled Excel workbook needs to be distributed to many users, there is a risk that the users remove the macros by saving the file in the wrong format. Depending on the functionality of the macros, this may cause annoyance among the users and it can be time consuming to adjust the error. Alternatively, you’ll get Emails with questions and complaints about your broken application from users that do not understand why some things are not working as they should. One way to get around this is to prohibit the users from saving the file as a non-macro enabled Workbook. The following short VBA-code ensures that the file is always saved in .xlsm-format:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    
    If Not SaveAsUI Then Exit Sub
    
    On Error GoTo ErrorHandler
    
    Cancel = True
        
    Dim FileName As String
    FileName = Application.GetSaveAsFilename(FileFilter:="Excel Macro-Enabled Workbook (*.xlsm), *.xlsm")
        
    If FileName = "False" Then Exit Sub
        
    Application.enableEvents = False
    ThisWorkbook.SaveAs FileName:=FileName, FileFormat:=xlOpenXMLWorkbookMacroEnabled
        
ErrorHandler:
    Application.enableEvents = True
End Sub

The setup

First, it’s important that this code is placed under ThisWorkbook in the VBA project. Otherwise, the procedure will not get called when the user is trying to save. The Workbook_BeforeSave procedure is called immediately before the workbook is saved, and you can read more about it here. In the code we are trying to swap the default Save-behaviour for our own, which is done by intercepting the Save-event and adding our custom code. The parameters passed to the Workbook_BeforeSave are:

  • SaveAsUI. A boolean parameter which indicates whether the “Save As”-dialog should be displayed.
  • Cancel. Indicates whether or not the user has cancelled out of the “Save As”-dialog.

The first line of the code checks if SaveAsUI is True. If it’s True, it means that Excel is going to display a “Save As”-dialog and the user will have the chance to pick a filename, location and file format. If it is False, the user will not be prompted. The whole point of the code is to change the default “Save As”-dialog from Excel to our own version which only has the selections of file formats that we allow. Since the UI will not be displayed if the SaveAsUI is False, we can simply exit the procedure here. The workbook will just be saved as normal with its current format, name and location.

Create a custom “Save As”-dialog

...
    Cancel = True
        
    Dim FileName As String
    FileName = Application.GetSaveAsFilename(FileFilter:="Excel Macro-Enabled Workbook (*.xlsm), *.xlsm")

    If FileName = "False" Then Exit Sub
...

With Application.GetSaveAsFilename we create our own “Save As”-dialog with just the “Excel Macro-Enabled Workbook (*.xlsm)”-option enabled. The user can now specify filename and the location of the file but will be restricted to only selecting the Macro-enabled workbook format. Read more about the options you can set here. After the user has finished, GetSaveAsFilename will return the full filename along with the path that the user has specified.

By setting the Cancel parameter to True, we ensure that Excels own “Save As”-dialog won’t be displayed when this code has finished executing. Excel will interpret it as if the user has cancelled out of the dialog.

If the user has pressed Cancel we simply exit the procedure. In case the user cancels out of the dialog the return value will be False. Since we assign it to a String it will be casted to a string representation (“False”), which is why we do a string comparison here.

Save the workbook

...
    Application.enableEvents = False
    ThisWorkbook.SaveAs FileName:=FileName, FileFormat:=xlOpenXMLWorkbookMacroEnabled
        
ErrorHandler:
    Application.enableEvents = True
...

This last part of the code performs the actual saving. The FileName variable now holds the whole location, filename and the file format. The SaveAs procedure takes a lot more optional arguments (More info), but for our simple example these are sufficient. In order to spare the user from clicking through some extra dialogs we disable events during the save.

Conclusion

That’s it. This code will ensure that users don’t accidentally save a Macro-enabled workbook as a non-Macro enabled workbook. The GetSaveAsFilename and SaveAs calls allows for a lot more configuration, such as setting your own file name, change titles, password protection etc. But for the simple use case of keeping the users from accidentally disabling the macros in the workbook by saving in the wrong format, this does a good job.