MrExcel Publishing
Your One Stop for Excel Tips & Solutions

VBA code to prevent Ssheet being saved as something else?

Posted by Paul on March 23, 2000 3:35 PM


I have written a small, S.Sheet and would like to stop people using the Save As. Command. Or if they do use the Save As command and rename the file the programme will not run. Any ideas.


Posted by Simon on March 24, 2000 3:35 AM

I use following code to stop people from saving my workbooks, if they use the Save or Saveas buttons/menu options. it has to go in the ThisWorkbook object.

Private Sub workbook_beforesave(ByVal SaveAsUI As Boolean, cancel As Boolean)
MsgBox ("Sorry you cannot save this workbook using this method")
SaveAsUI = False
cancel = True
End Sub

Posted by Paul on March 24, 2000 9:34 PM

Many thanks Simon for you help! Works great!

Posted by Paul on March 26, 2000 5:53 PM

One more question Simon Re: Not saving changes

The code works great. However is there some way to save changes to one sheet. But have the document protected against being saved, by using your code?


Posted by Simon on March 26, 2000 10:13 PM

Re: One more question Simon Re: Not saving changes

I am working on this myself, at the moment I have a command button on the worksheet I need to save, this is linked to a macro which just copies the sheet to a new workbook and then I can save the new workbook. I find this method a bit messy but it works for the moment, I would welcome any suggestion for a better method.