Help on Macro to Prohibit saving the workbook

**Big_Slick** · 09-07-2014, 03:46 AM

Hello,

I have a excel workbook that needs to go on a company public computer. I have added the code below to prohibit people from copying this to other drives

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

MsgBox "You can't save this workbook!"
Cancel = True

End Sub

The code works fine, but my question is this. On one page of the workbook, I have columns of Employee names that I use to populate drop down boxes on other sheets, I need this one sheet to be able to be updated and it doesn't need any type of protection. Can I add to the above code, that will allow to save changes to the one sheet when closed, but still not allow the workbook to be saved.

Perhaps if I allowed saving to that particular drive, using the same name only, but I am not sure how to go about that.

Thanks.

R

**HaHoBe** · 09-07-2014, 04:26 AM

Hi, Big_Slick,

the macro won´t be of much help when the users copy the workbook via Windows-Explorer.

You might enable saving on any change on the worksheet. Maybe try putting the code behind that sheet:

Please Login or Register  to view this content.

Please mind that you should include a check if the worksheet is activated that any previous changes should be undone unless you want to make it possible to save those changes as well. Or you may try using a global variable to decide whether to store or not which may be turn False on Activating the sheet and True when De-Activing the sheet and may be inserted as well in a standrad module as in BeforeSave instead of True.

Please have a look at Forum Rule #3 and wrap your procedure with code-tags as requested there.

Ciao,
Holger

**MonkeyDeath** · 09-07-2014, 04:41 AM

Depending on your compnies IT user group policies, you could set the security to every can just read the file/folder except you/admin of the file. This sill stop copying of the file in explorer etc. Its not 100% fool proof but it will stop 99.9% of normal users

This is done by the network administrator