How to force users to save an Excel workbook as a macro-enabled file?

When you save an Excel workbook, it defaults to the .xlsx file format, which does not support macros and will remove any macro code in the workbook. 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?

Force users to save as a macro enabled workbook with VBA code

Force users to save as a macro enabled workbook with VBA code

To ensure the workbook is saved as a macro-enabled workbook by default, you can use the following VBA code. Please follow these steps:

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
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 this workbook, it will be saved in the Excel Macro-Enabled Workbook format by default, as shown in the following screenshot: