Workbook_BeforeSave question

[Guest]

Does it make sense to force a SAVEAS during the workbook_beforesave event?
We have a "template" (not in the Excel terminology) that we don't want
overwritten. Is there a recommended way to ensure that doesn't happen with
the Workbook_beforesave or another method?

Thanks,

 

[Ron de Bruin]

Hi Barb

You can use this

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
If SaveAsUI Then
'do nothing
Else
Cancel = True
End If
End Sub

But if you use save as to another name the code is also working
Maybe add a check for the template name

 

[moon]

Make the file ReadOnly by changing it's properties in Windows Explorer.

 

[Bob Phillips]

Yes it can do. If you notice this event procedure has a SaveAsUI argument,
which tells you if it is being saved as (True) or not (False). You can test
this and if false set Cancel to True and do a SaveAs (disable events first
though).

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[Guest]

If I check the ActiveWorkbook.name against the predefined name of the
template and it matches, how do I force a SaveAs?

 

[Ron de Bruin]

Maybe this

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
If ThisWorkbook.Name <> "template.xls" Then Exit Sub
If SaveAsUI Then
'do nothing
Else
Cancel = True
End If
End Sub

Sub Savethetemplate()
Application.EnableEvents = False
ThisWorkbook.Save
Application.EnableEvents = True
End Sub

 

[Guest]

Just some thoughts:

Even if the SaveAsUI is true, the default file name will be this file's name
and allowing the user to select a name will not prevent them from overwriting
the file (they can select the same name).

Disabling macros disables any programmed protection.

Nothing will prevent the user from copying over another file over this one
using the operating system.


Ron,
what is this line supposed to do?
If ThisWorkbook.Name <> "template.xls" Then Exit Sub

it basically says if the name is not template.xls, then you can't save.
that didn't sound like anything the OP wanted.

In any event, I would think you would have to cancel the save triggering the
event, then use getsaveasfilename to let the user pick a name, then determine
if the name is acceptable. If it is, save. If it isn't prompt again or exit
the routine.

 

[Ron de Bruin]

Hi Tom

As I understand it the OP want not to overwrite the original template.

If he Save as the template a different name he can save the file now because of this line
If ThisWorkbook.Name <> "template.xls" Then Exit Sub

But I agree with your thoughts
I will never use it

A real template is another option for the OP maybe

 

[Guest]

I see what you are saying. Nonetheless, with that approach, the user can
still select the name template.xls and save the workbook.

 

[Ron de Bruin]

Yes you are correct Tom

It is not a good way