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
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
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
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
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 ...
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.
That’s it. This code will ensure that users don’t accidentally save a Macro-enabled workbook as a non-Macro enabled workbook. The
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.