Force users to save spreadsheet with active macroes.


2 steps, a friend of mine suggested that i put a save as button in the file, that activates the function button F12 (Save as)

If you add the code below in VBA you’ll make sure the file is saved as a .xlsm

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

Dim txtFileName As String

‘1. Check of Save As was used.

If SaveAsUI = True Then

Cancel = True

‘2. Call up your own dialog box.  Cancel out if user Cancels in the dialog box. (“blahh” is the suggested filename)

txtFileName = Application.GetSaveAsFilename(“blahh”, “Excel Macro-Enabled Workbook (*.xlsm), *.xlsm”, , “Save As XLSM file”)

If txtFileName = “False” Then

MsgBox “Action Cancelled”, vbOKOnly

Cancel = True

Exit Sub

End If

‘3. Save the file.

Application.EnableEvents = False

ThisWorkbook.SaveAs Filename:=txtFileName, FileFormat:=xlOpenXMLWorkbookMacroEnabled

Application.EnableEvents = True

End If

End Sub