Protect a Workbook

[Dave]

Hi,

I have a file that is opened by a user and a macro is run to protect it so
when the file is sent out to another user, none of the cells can be selected.
The issue I am having is the macro protects the workbook but when the user
opens the workbook in Read only mode, he/she is still able to unprotect it
with out using a password. I would like the unprotect button in excel 2007 to
be grayed out when the user opens the file in read Only mode. It was my
understanding that if wrterespassword was used that the user would be
prompted to open in read only or enter the password. If the user selects read
only, they should not be able to unprotect teh workbook. Please see my code
below. What am I missing here? Any assistance is greatly appreciated.

ActiveWorkbook.Protect Password:="Password", structure:=True,
Windows:=False
ActiveSheet.Protect DrawingObjects:=True, Contents:=True,
Scenarios:=True _
, AllowSorting:=True, AllowFiltering:=True
ActiveSheet.EnableSelection = xlNoSelection

Application.DisplayAlerts = False
ActiveWorkbook.SaveAs FileFormat:=xlNormal, ReadOnlyRecommended:=True, _
WriteResPassword:="Password"

 

[Paul C]

You missed the Password on the sheet protect. The workbook and worksheet
protect are two different things.

ActiveSheet.Protect Password:="password",DrawingObjects:=True, _
Contents:=True, Scenarios:=True _
, AllowSorting:=True, AllowFiltering:=True
ActiveSheet.EnableSelection = xlNoSelection

 

[Dave]

thanks, I knew I was missing something. It worked

 

[Dave Peterson]

You protected the workbook's structure using a password. That means that the
user can't rearrange sheets (or add or delete more sheets) without unprotecting
the workbook's structure.

But you protected the activesheet without a password. That means that the user
can change values to any of the unlocked cells in that protected sheet. And
they only have to unprotect the sheet to be able to change any of the cells
(unlocked or locked). And since you didn't provide a password, it's pretty
simple to do.

Using:

ActiveSheet.Protect DrawingObjects:=True, Contents:=True, password:="hi there",
_
Scenarios:=True, AllowSorting:=True, AllowFiltering:=True

would make it slightly more difficult to unprotect that sheet. But there are
lots of ways to break this password.

And saving the workbook with WriteResPassword means that the user can still open
the workbook in readonly mode. Then make as many changes as they want (and that
your other protection settings allow) and save as a new name.

All of these levels of protection are independent of the others--as long as the
user can open the workbook.

=====
Just a note--the workbook protection (tools|Protection|protect sheet and
tools|Protection|protect workbook are both easily broken. Don't trust them to
keep the dedicated from your intellectual property.

The password to open is more difficult to break--but there are lots of sites on
the internet that will make it easy if the person has a few bucks and really
wants to open your file.