Using Off365 2016 for MAC. Got a solutions?
How to force users to save as a macro enabled workbook?
When you save an Excel workbook, it will be saved as xlsx file format by default, and this file format will get rid of the macro codes from the workbook if there are multiple codes. To keep the codes, you should save the workbook as Excel Macro-Enable Workbook format. How could you force users to save as a macro enabled workbook?
Recommended Productivity Tools for Excel
Office Tab: Bring powerful tabs to Office (include Excel), just like Chrome, Safari, Firefox and Internet Explorer. Save you half the time, and reduce thousands of mouse clicks for you. 30-day Unlimited Free Trial
Kutools for Excel: Save 71% of your time and solve 82% Excel problems for you. 300+ advanced tools designed for 1500+ work scenario, make Excel much easy and increase productivity immediately.60-day Unlimited Free Trial
To save the workbook as macro enabled workbook by default, the following VBA code may help you, please do as this:
1. Hold down the ALT + F11 keys to open the Microsoft Visual Basic for Applications window.
2. Then double click ThisWorkbook under the VBAProject section to open a new blank module, and then copy and paste the following code into the module:
VBA code: Force to save workbook as macro enabled workbook by default:
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) 'Updateby Extendoffice 20161222 Dim xFileName As String If SaveAsUI <> False Then Cancel = True xFileName = Application.GetSaveAsFilename(, "Excel Macro-Enabled Workbook (*.xlsm), *.xlsm", , "Save As xlsm file") If xFileName <> "False" Then Application.EnableEvents = False ActiveWorkbook.SaveAs Filename:=xFileName, FileFormat:=xlOpenXMLWorkbookMacroEnabled Application.EnableEvents = True Else MsgBox "Action Cancelled" Cancel = True Exit Sub End If End If End Sub
3. Then save and close this code window, from now on, when users save as this workbook, it will be saved as Excel Macro-Enabled Workbook format by default as following screenshot shown:
Recommended Productivity Tools
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
To post as a guest, your comment is unpublished.
To post as a guest, your comment is unpublished.· 3 months agofileformat: =52 is a nicer was as making macro enabled workbook. fileformat:=51 is normal workbook.
To post as a guest, your comment is unpublished.· 10 months agoI'm using Excel 2016 for Mac, and when I use this VBA code to achieve the "force save as .xlsm" I get the following error:
Run-time error '1004':
Method 'GetSaveAsFilename' of object '_Application' failed
Any guidance someone could provide around this error and how I may need to adjust the code to work on the Mac version of Excel would be greatly appreciated!
To post as a guest, your comment is unpublished.· 11 months agoNice one!
Saved me a lot of headache! :)
To post as a guest, your comment is unpublished.· 1 years agoSaved me a lot of pain - thanks so much