Save As Macro-Enabled Workbook

[James Fedor]

I have two macro-enabled templates and I want to force save as macro-enabled workbooks. The following code works fine in one template but not in the other template. Any ideas why it won't work in both templates?


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

If ThisWorkbook.Saved = True Then
Exit Sub
Else
If SaveAsUI = True Then
Cancel = True
txtFileName = Application.GetSaveAsFilename(ThisWorkbook.FullName, "Excel Macro-Enabled Workbook (*.xlsm), *.xlsm", , "Save As XLSM file")
If txtFileName = "False" Then
MsgBox "Action Cancelled", vbOKOnly
Cancel = True
Exit Sub
End If
Application.EnableEvents = False
ThisWorkbook.SaveAs Filename:=txtFileName, FileFormat:=52 'xlOpenXMLWorkbookMacroEnabled
Application.EnableEvents = True
End If
End If
End Sub

 

[starl]

What happens in the template where it doesn't work? Is this the code from the template where it doesn't work?

 

[James Fedor]

The code is the same in both workbooks, but I think I found the problem. There is only one worksheet in the workbook where it didn't work, and that worksheet was protected. I changed the code to unprotect the sheet before saving and then reprotect it after saving, and it now works fine. Thanks!

 

[starl]

weird.. sheet protection shouldn't make a difference. But if you got it working, that's all that matters.