Workbook Protection

[Patrick Simonds]

I am running an Auto_Open macro which calls up a password Dialogbox. If the
person has the correct password they can open and edit the workbook. If they
do not have the correct password, they must be able to open and view the
document but not be able to make any changes.

What I need is something which will open the Workbook as Read Only and all
cells on all worksheets locked.

I know that by opening the workbook as read only they would not be able to
over write my original workbook but they would still be able to make changes
and print those changes. I also know that if I allow the workbook to open
(not Read Only) and lock all the cells through the Macro they could then
same the workbook and then all the cells would be locked next time it was
opened. I have some cells which are normally locked so even for normal usage
worksheet protection is turned on.

 

[STEVE BELL]

Patrick,

There are 2 things that could make this easier.

In the Thisworkbook module:

Create a worksheet change event and put in

Application.Undo

So that anytime a change is attempted it will be undone.

The second is to set up a workbook before close event to not save

Thisworkbook.Close False

this way if any changes are made, they won't be saved.

Have both of these encased in an If statement that checks if the password
has been used.

You can do this easily in the workbook open event by setting a public
variable to "OK" or "NotOK". This variable will persist until the workbook
is closed.

Another thing to add is setting all the sheets to xlveryhidden on close,
leaving a warning sheet open in case they disable macros.

And on open the sheets can be made visible if the password is used.

And definitely protect the VBE project with a password so no one can mess
with the code.

Let us know if any of this makes sense...

There is also a way to protect locked cells in code so that only the code
can change them.

 

[Patrick Simonds]

My concern is that if I use your suggestion, wont this apply to any one who
opened the workbook. I want to create one experience if you do not have the
password (the ability to view the Workbook but no be able to make ANY
changes, or be able to save the current document, since a higher level of
worksheet protection is applied) and another for someone who has the
password. This person would be allowed to make changes and save the
workbook.

I have figured out the protection issue (all cells are locked for those
without the password) but can not figure out how to prevent the first group
from being able to save the document (with the higher level of cell
protection) while preserving the ability for the authorized users.

Currently I am using a dialogbox which asks if you want to Read Only or
Edit. If you click on Read Only everything gets locked down. If you click on
Edit you must provide a password and are allowed into the normal workbook.

 

[STEVE BELL]

Patrick,

Remember that password protection is marginal. Any determined user can
crack most passwords.
Putting a password on the VBE project is a little stronger.

Like I said - during the Workbook Open event you capture the password with a
Public variable.
This retains its value until the workbook is closed.

You can also add code to protect/unprotect, hide/show, etc depending on
password.

If the sheets are xlveryhidden (with only a warning sheet showing) than only
the warning sheet will show if macros are turned off. (But a determined
user can get around this)

Option Explicit
Public pswrd As String

Private Sub Workbook_BeforeClose(Cancel As Boolean)
' this closes the workbook without saving changes if password is wrong

If pswrd = "NotOK" Then
ThisWorkbook.Close False
End If
' further code here
End Sub

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)
' this prevents saving the workbook if the password is wrong
If pswrd = "NotOK" Then
ThisWorkbook.Close False
End If
' further code here
End Sub

Private Sub Workbook_Open()
'this asks for password and than does what ever depending

pswrd = InputBox("Enter Password", "Password")
If pswrd = "xxxxx" Then
pswrd = "OK"
' further code here
Else: pswrd = "NotOK"
' further code here
End If
End Sub