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?
- Reuse Anything: Add the most used or complex formulas, charts and anything else to your favorites, and quickly reuse them in the future.
- More than 20 text features: Extract Number from Text String; Extract or Remove Part of Texts; Convert Numbers and Currencies to English Words.
- Merge Tools: Multiple Workbooks and Sheets into One; Merge Multiple Cells/Rows/Columns Without Losing Data; Merge Duplicate Rows and Sum.
- Split Tools: Split Data into Multiple Sheets Based on Value; One Workbook to Multiple Excel, PDF or CSV Files; One Column to Multiple Columns.
- Paste Skipping Hidden/Filtered Rows; Count And Sum by Background Color; Send Personalized Emails to Multiple Recipients in Bulk.
- Super Filter: Create advanced filter schemes and apply to any sheets; Sort by week, day, frequency and more; Filter by bold, formulas, comment...
- More than 300 powerful features; Works with Office 2007-2019 and 365; Supports all languages; Easy deploying in your enterprise or organization.
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:
You are guest
or post as a guest, but your post won't be published automatically.
- To post as a guest, your comment is unpublished.· 6 months agoAny way to have this VBA in a file that is a true template file type?
- To post as a guest, your comment is unpublished.· 6 months agoAny way to have this VBA in a template file
- To post as a guest, your comment is unpublished.· 1 years agoKeep getting a 1004 ERROR.
Using Off365 2016 for MAC. Got a solutions?
- To post as a guest, your comment is unpublished.· 1 years agoHello, Jonathan,
This code works well in Windows Office, but it not tested in MAC.
You should search for a code for working in MAC.
- To post as a guest, your comment is unpublished.· 1 years 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.· 1 years 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.· 1 years agoNice one!
Saved me a lot of headache! :)
- To post as a guest, your comment is unpublished.· 2 years agoSaved me a lot of pain - thanks so much
- To post as a guest, your comment is unpublished.· 3 years agoThis was so useful and worked like a charm thank you :)