Use File as Template, Force Save as on Close

[helpexcel]

I have a file I want to make read only, so I selected that in the options tab. How do I change this code, ActiveWorkbook.Save, to force SaveAs so that the template is never written over? thanks.

 

[helpexcel]

I don't know if I should've noted that I have code in ThisWorkbook to remove menubar when the file closes.

Private Sub Workbook_BeforeClose(Cancel As Boolean)

Application.ScreenUpdating = False
Application.DisplayAlerts = False

Dim cbWSMenuBar As CommandBar

On Error Resume Next
    Set cbWSMenuBar = Application.CommandBars("Worksheet menu bar")
    cbWSMenuBar.Controls("Tool Menu").Delete

Application.DisplayAlerts = True
Application.ScreenUpdating = True

End Sub

 

[GWteB]

You might consider to save your template as a real Excel template, so when it is accessed it opens as a yet unsaved file (Book1).

 

[helpexcel]

OK, I updated the code and this forces the save as on close. I 'm going to try changing it to a template file, assuming I just then save with the same name to update it with changes I may make?

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    If Not SaveAsUI Then Exit Sub
    Cancel = True
End Sub

On Error GoTo ErrorHandler
   
    Dim FileName As String
    FileName = Application.GetSaveAsFilename(FileFilter:="Excel Macro-Enabled Workbook (*.xlsm), *.xlsm")
   
    If FileName = "False" Then Exit Sub
   
    Application.EnableEvents = False
    ThisWorkbook.SaveAs FileName:=FileName, FileFormat:=xlOpenXMLWorkbookMacroEnabled

ErrorHandler:
    Application.EnableEvents = True

 

[GWteB]

Did you actually try it?
Most of the code is outside the before save event handler

 

[GWteB]

to force SaveAs so that the template is never written over

That is not a guarantee, because the user can choose the same name.

I 'm going to try changing it to a template file, assuming I just then save with the same name to update it with changes I may make?

Yes, but be sure you OPEN the template instead of NEW'ing it.