Prevent users from saving a workbook

B

Brent Mahaffy

I have an Excel workbook with many sheets that many users
use. I want to prevent them from being able to save the
workbook and worksheets. Any suggestions?
 
D

Don Guillett

I think I have seen this.

in the ThisWorkbook module>before save event

cancel=true
 
R

Robert N

Since the method Don mentioned puts a macro in your
workbook, it will only work if each user has their macro
security is set to low or medium (and they enable
macros.)

If you just put the line of code Don mentioned you
yourself wont be able to save it.

Two possible ways to work around this are:
Method 1:
1 Open Excel
2 Click the Tools Menu
3 Click the Macros Sub Menu
4 Click the Security option
5 Click the Security Level Tab
6 Change the security level to Medium or High
7 Click Ok Button
8 Open the workbook you want to prevent users from
saving
9 If you chose Medium in Step 6, Click the button that
disables macros.
10 Go to the Visual Basic Editor, and add the line
Cancel = true to the ThisWorkbook Module, Before Close
event.
11 Save Workbook.

Method 2:
1 Open the workbook you want to prevent from being
saved.
2 Go to the Visual Basic Editor, and add the following
line to the ThisWorkbook Module, BeforeClose event:
If Application.UserName <> "?" Then Cancel = True
make sure your username is between the "" instead of
the ? If you don't know what your user name is, go to the
Excel File Menu and select Properties. The username is
listed under Author.
3. Save Workbook.

If you have any questions about these methods, Post a
reply.

HTH
Robert
 
B

Brent Mahaffy

This is what I have going on and I can still save

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean,
Cancel As Boolean)
Cancel = True
End Sub
 
R

Rob

Your must have macro security set to High.
(See instructions posted by Robert below)

Robert N
 
B

Brent Mahaffy

Actually the Security is set to low
-----Original Message-----
Your must have macro security set to High.
(See instructions posted by Robert below)

Robert N




.
Click to expand...
 
R

Rob

That is strange, any time I try the same thing, I cannot save. Perhaps I
have a different version than you. I am using Excel 2000 SP-3

Robert
 
R

Rollin_Again

After changing the macro security level did you re-save the workbook?
Try re-saving with the correct security setting, closing the workbook
and then re-opening the workbook to make sure the changes have take
place.

Also, are you sure you place the code the "ThisWorkbook" module instea
of a stand alone code module?

Rolli
 
G

Guest

I've got 03
-----Original Message-----
That is strange, any time I try the same thing, I cannot save. Perhaps I
have a different version than you. I am using Excel 2000 SP-3

Robert




.
Click to expand...
 
H

Harald Staff

Then why should they spend time on it ?

In the File > Save As menu there's a place (version dependent location,
Tools > General options, upper right perhaps) to define a "password to
modify" password, forcing users to save an altered version as something else
unless they are you. Is that what you want ?

HTH. Best wishes Harald
 
D

Dave Peterson

Are you sure you put the code under ThisWorkbook--not in a general module and
not under a worksheet?
 
Top