BeforeSave macro that STOPS the save.

[evil_moses]

Hi,

I need to place some code into the workbook beforesave macro that ensures that the file isn't saved. I have written a template for somebody, and don't want them to be able to fill the template and then overwrite it by saving (trust me, they would)

Anyone able to help?

 

[VoG]

Try this

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
If SaveAsUI = False Then
    Cancel = True
    MsgBox "You can't Save, only Save As", vbExclamation
End If
End Sub

 

[evil_moses]

Thanks.

Is there anyway to prevent a save as aswell?

 

[VoG]

Sure

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Cancel = True
MsgBox "You can't save this file", vbExclamation
End Sub

This (and the previous one) will only work if the user has macros enabled.

 

[evil_moses]

That's great thanks. For anyone else reading this if having the same problem, it is worth having the following code to stop excel asking if you want to changes when you exit

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    ActiveWorkbook.Close savechanges:=False
End Sub

 

[evil_moses]

VoG,

Last question!

Is it possible to create a macro that will only let you save as, but will not let you save as the original filename?

Using the first part you replied I cannot save, but can overwrite the file by saving as the orginial filename within save as.

Am I making sense?

 

[VoG]

I understand what you mean but I can't think of a straightforward way of doing that.

 

[Greg Truby]

Note that when you use the term "template" in this forum, we will think that you mean that you have created an XLT file. This means that the default behavior of Excel will generally prevent what you're describing. If they simply use the FILE | NEW... and pick your template from a list of templates, Excel will default to having them save the file as a new XLS file, leaving your XLT file in tact.

Since you are experiencing this problem, or anticipating it, I can only assume that either they are opening the XLT as a template or else you are mis-using the term "template" and what you have is an XLS file.

If the former describes your situation, then a change in process would fix *most* of it (people can still "do it wrong"). If the latter is your situation, then when *you* save the file, click Save As and on the top right (in XL2003) there is a button tools (it's on the lower left in XL2007). Clicking the tools button opens a dropdown were you can pick general options... and set a password to modify. This will force the user to either provide the password or else save using a new file name.

And all of this uses built-in features meaning that you do not have to force the user to enable macros in order for your solution to be effective. :wink:

 

[evil_moses]

Did not know about an xlt - very useful. That mitigates all of my concerns regarding overwriting my "template". Thanks.