Workbook security

M

Min

I have created a workbook with about 50 sheets in it. How do I make it
completely secure? I want it to be read only, nothing at all editable and
preferably for the code not to be viewed.
I need to be able to edit it on a fairly regular basis so I don't
particularly want to have to enter a different password for each sheet.
Is this possible?
 
R

Ron Coderre

First, a word about Excel protection. You state that you want your file to
be "completely secure". That's not really possible. It's fairly common
knowledge that Excel files do not have "bullet proof" security. The general
intent of Excel passwords is to prevent accidental meddling and provide
somewhat of an obstacle to intentional meddling. It's akin to locking the
door to your home but not barricading the windows. Any motivated person with
fairly unimpressive technical skills could find a way in with only moderate
effort.

Now, if you're looking to cut down on the number of steps involved in
protecting 50 sheets, their contents, and the workbook structure, here's what
I do:

I make a Lock/Unlock workbook who's only function is to eithe lock-up or
unlock the workbook and sheet of the Excel file. Then keep that file in a
safe place.

Does that help?
***********
Regards,
Ron

XL2002, WinXP
 
M

Min

Thanks Ron,
You said ..."I make a Lock/Unlock workbook who's only function is to eithe
lock-up or unlock the workbook and sheet of the Excel file."
How do I do this?
 
M

Min

Please could you tell me how to make it as secure as possible - enough to
prevent the average user getting into!
 
R

Ron Coderre

Since you indicated that you have "code" in your workbook, I'm guessing that
you have some vba skills.

Here's something to get you started.....

Copy this code into the General Module of a NEW workbook and save the
workbook separately. Note: you'll need to change the workbook name and
password constants to match your situation.

'-------------Start of Code-------------
Sub UnlockMyWkbkAndSheets()
Const strPwd As String = "password"
Const strWkbk As String = "MyWorkbook.xls"

Dim sSht As Worksheet

Application.ScreenUpdating = False

With Workbooks(strWkbk)
If .ProtectStructure = True Then
On Error Resume Next
.Unprotect Password:=strPwd
If Err.Number <> 0 Then
MsgBox "Workbook: " & .Name & " could not be unprotected"
End If
End If
For Each sSht In .Worksheets
If sSht.ProtectContents Then
On Error Resume Next
sSht.Unprotect Password:=strPwd
If Err.Number <> 0 Then
MsgBox "Sheet: " & sSht.Name & " could not be unprotected"
End If
End If
Next sSht
End With
End Sub

Sub LockMyWorkbookAndSheets()
Const strPwd As String = "password"
Const strWkbk As String = "MyWorkbook.xls"

Dim sSht As Worksheet

Application.ScreenUpdating = False

With Workbooks(strWkbk)
For Each sSht In .Worksheets
If sSht.ProtectContents = False Then
sSht.Protect Password:=strPwd
End If
Next sSht
If .ProtectStructure = False Then
.Protect Password:=strPwd
End If

End With
End Sub
'-------------End of Code-------------

When BOTH workbooks are open.
To UNLOCK the workbook, run the UnlockMyWkbkAndSheets code.
To LOCK the workbook, run the LockMyWkbkAndSheets code.


Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP
 
Top