All sheet: read-only protection

[scubadiver007]

Hello,

I have gone through the various password protection methods but I can't get the workbook to do what I want.

I want to make sure every tab is read-only and any accidental modification requires a password.

"Protect sheet" protects the current sheet and I don't want to use a separate password for every tab. "Protect workbook" requires a password before it is opened.

Any ideas?

[royUK]

You have to protect the sheet individually. You can automate this with a macro

[Domski]

Something like this:

Sub Protect_Sheets()

Dim strPWord As String
Dim wsEachSheet As Worksheet

strPWord = InputBox("Enter password to lock sheets")

For Each wsEachSheet In ThisWorkbook.Worksheets
    wsEachSheet.Protect strPWord
Next wsEachSheet

End Sub

Sub Unprotect_Sheets()

Dim strPWord As String
Dim wsEachSheet As Worksheet

strPWord = InputBox("Enter password to unlock sheets")

On Error GoTo ErrorHandler

For Each wsEachSheet In ThisWorkbook.Worksheets
    wsEachSheet.Unprotect strPWord
Next wsEachSheet

CleanExit:
Exit Sub

ErrorHandler:
MsgBox Err.Description
GoTo CleanExit

End Sub

Dom

[scubadiver007]

Clever but the slight drawback is that there is no way to know if the password can be checked when unprotecting the worksheet so I am storing the string on a spreadsheet using the protect code and then I want to compare the string in the unprotect code.

For some reason my If statement isn't working.

Dim strPWord As String
Dim wsEachSheet As Worksheet

On Error GoTo ErrorHandler

If Worksheets("Sheet1").Range("O4").Value <> strPWord Then Exit Sub

Else

For Each wsEachSheet In ThisWorkbook.Worksheets
    wsEachSheet.Unprotect strPWord
Next wsEachSheet

End If

MsgBox "Worksheets unprotected"

Unload UserForm2

Unload UserForm1


CleanExit:
Exit Sub

ErrorHandler:
MsgBox Err.Description
GoTo CleanExit

[Domski]

Sorry, don't follow what you mean.

Dom

[scubadiver007]

I have created a password using the protect code but I have tried a completely different string of characters to unprotect the sheets and it works.

[Domski]

Are you sure? If the sheets were protected with a password after you ran the Protect_Sheets code there's no way the other code would unprotect them without the correct password even with Excel 2003's slack security.

Dom

[scubadiver007]

Sorry, might be my mistake. I'm not using an input box.

EDIT: its fine now, thanks.

[royUK]

Where does your code actually set a value to strPWord?