Saving an Unsavable Workbook

In my case, I'm the only one writing macro code and templates for others to use.

To ensure other users do or don't do certain things, I built a simple function that
checks whether Application.UserName contains my name, "Bonin".

If it does, then I might have the code ask whether I'm saving the file as a "user" or
as a "developer".

If it does not, then I typically force the user option.

I include this function wherever I need to. For today's example, I would put it in a
Workbook_BeforeSave() macro.
___

Of course, if you have multiple developers, you can expand the Application.UserName
checks to include many users, some perhaps with different privileges.

In the second scenario, that deletes the cell contents on open, wouldn't that then delete the valid contents entered by a user when they re-open the document? If true, then only a PDF printout of the spreadsheet would be viewable and be the only record of the valid contents, once the spreadsheet was opened the second time.

When writing complicated macros/code, I always includes a binary testing variable. I set it to true, while testing.
Then in the code itself, I use an If...then block to exclude running that section when Testvar = true.
When I'm done testing what I need, then I can reverse the variable in the if...then block, and work on another area.

This allows me to control what I want to run and what I want to isolate while testing.

At the end, I do have to go through the code and verify either: 1) all the testvar variable are correctly set, or 2) remove all the testing if...then blocks.

The last option (disabling the Event Handler) can actually be done within a macro!
If you create one macro to disable events, viz;

Sub DisableEvents ()
Application.EnableEvents = False
End Sub

and another to enable them

Sub DisableEvents ()
Application.EnableEvents = True
End Sub

you can run these macros like any other without having to open the Visual Basic Editor.

You can either include them in the template workbook (best in a separate "Administrators" module, or add them to your Personal.xlsm so you can run them anytime for any workbook.

Something like this

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim Ans As Integer
Dim Rng As Range

'List of cells
Const NotEmptyCells = "A1,A3,C13"
Set Rng = Range(NotEmptyCells)

'Format choice
Ans = MsgBox("Save As template ?", vbYesNoCancel + vbQuestion, "Save as... (format)")

'Stop default process
Application.EnableEvents = False

'
Select Case Ans
Case vbYes 'Template
Rng.ClearContents
ActiveWorkbook.SaveAs Filename:=Left(thisworbook.Name, InStr(".", ThisWorkbook.Name) - 1) & "_template.xltm", _
FileFormat:=xlOpenXMLTemplateMacroEnabled

Case vbNo 'All except template
If Application.CountA(Rng) = (Len(NotEmptyCells) - Len(Replace(NotEmptyCells, ",", ""))) Then
MsgBox "You must enter values into cells " & NotEmptyCells & Chr(13) & "FILE NOT SAVED.", vbOKOnly + vbExclamation, "ERRORS VALUES"
Else
'
ActiveWorkbook.Save
End If

Case vbCancel 'STOP

End Select

'Stop default process
Cancel = True

'Stop default process
Application.EnableEvents = True

'
Set Rng = Nothing
End Sub

ES

Why not just have a special exit password in one of the cells or could even bee another cell way off the user screen e.g. AZ1000000. This password is an otherwise invalid value for the cell, which is tested in the "Before Save" macro which if present deletes the password and then saves the workbook.