Restricting rights to save a spreadsheet

[GeneR]

I tried to save this once and received an error. I hope I didn't duplicate
my question.

I have a worksheet I created that imports data from one spreadsheet to
another, then runs several formula calculations on the retrieved data. I
want to be able to place in on our intranet and allow users to access it, but
not to be able to save any changes. I have restriced the cells, but also
want to restrict the worksheet so it cannot be saved to a hard drive or
anywhere else.

 

[Ken Puls]

Unfortunately, I don't believe that you can prevent the workbook from
being copied off your network. You can make it more difficult, by using
some VBA to prevent saving from within Excel, but this will have no
effect if someone wants to just copy the file when the workbook is
closed. (Baring, of course, and security that your network admins may
be able to set up.)

If you want to disable saving from within Excel, you could use the
following code to do so:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)
If Not Application.UserName = "Ken Puls" Then Cancel = True
End Sub

Update my name to your username as defined in Tools|Options|General ->
Usename. The code would go in the ThisWorkbook module of your VB
Project. To get it there:

-Press Alt+F11 to enter the Visual Basic Editor
-Press Ctrl+R to display the explorer if it is not showing
-Find your project and drill into the ThisWorkbook module
-Paste the code there. Dont' forget to modify your username

This code means that only you will be able to save the workbook, and
then only if you are at a PC which uses the MS Office username I pointed
you to above. It will discard changes silently. You will also get
macro warnings from this point forward.

HTH,

Ken Puls, CMA - Microsoft MVP (Excel)
www.excelguru.ca

 

[Leandro]

Hummmm. This macro did not work for me. All Save buttons are still activated.
Should not there be a "End If" in the code?

 

[Gord Dibben]

Leandro

Ken's code does not hide or make unavailable the Save buttons.

It just cancels any Save if user name is not correct.

Doesn't need an "End If"

Stick one in and see what happens just for your own edification.


Gord Dibben MS Excel MVP