Confused here Prevent Saving File - but allow ONLY File SAVEAS Met

[Guest]

I have the two event codes below. I do not want the current file to be saved
with the opening Filename. What am I missing in order to after the user gets
the message below he is able to then do a File SAVEAS and (must) enter a new
filename?

Private Sub Workbook_BeforeClose(Cancel As Boolean)
MsgBox "This is a Template File. Do a File, SaveAs and provide a New
FileName with Date"
Cancel = True
End Sub

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
MsgBox "This is a Template File. Do a File, SaveAs and provide a New
FileName with Date"
Cancel = True
End Sub

 

[Earl Kiosterud]

JMay,

The problem with the BeforeSave solution is that it will get invoked with a Save-as as well,
leaving the user no way to save. He'll get mad. Or go mad -- not sure which. Also, if
macros haven't been allowed at the opening of the file (entirely at the choice of the user),
then you're out of luck.

Have you considered marking the original file Read-only? You do that in the folder in which
it lives. That disallows saving over the file -- only save-as with another name is
permitted. A user might "fix" the problem by removing the read-only attribute, so you
should keep an unused copy of the workbook in a safe place -- it's just a matter of time
before an industrious user mucks up the works. Or you can use your network rights to
protect the file.

Another option is to save the original workbook as a template. Now instead of opening the
existing workbook, the user uses File - New, and selects that template. If he does a save,
it comes up Save-as -- it gets saved as a workbook. The template is protected (unless the
user uses Save-as and elects to save it AS a template, in which case YOU go mad). Using
read-only on the template will reduce the chance of that. You should still keep an unused
copy of template somewhere where no one, well except you, can find it.

--
Earl Kiosterud
www.smokeylake.com

Note: Some folks prefer bottom-posting.
But if you bottom-post to a reply that's
already top-posted, the thread gets messy.
When in Rome...

 

[Guest]

Great Info Earl, Thanks very much;;

JMay,

The problem with the BeforeSave solution is that it will get invoked with a Save-as as well,
leaving the user no way to save. He'll get mad. Or go mad -- not sure which. Also, if
macros haven't been allowed at the opening of the file (entirely at the choice of the user),
then you're out of luck.

Have you considered marking the original file Read-only? You do that in the folder in which
it lives. That disallows saving over the file -- only save-as with another name is
permitted. A user might "fix" the problem by removing the read-only attribute, so you
should keep an unused copy of the workbook in a safe place -- it's just a matter of time
before an industrious user mucks up the works. Or you can use your network rights to
protect the file.

Another option is to save the original workbook as a template. Now instead of opening the
existing workbook, the user uses File - New, and selects that template. If he does a save,
it comes up Save-as -- it gets saved as a workbook. The template is protected (unless the
user uses Save-as and elects to save it AS a template, in which case YOU go mad). Using
read-only on the template will reduce the chance of that. You should still keep an unused
copy of template somewhere where no one, well except you, can find it.

--
Earl Kiosterud
www.smokeylake.com

Note: Some folks prefer bottom-posting.
But if you bottom-post to a reply that's
already top-posted, the thread gets messy.
When in Rome...