MrExcel Publishing
Your One Stop for Excel Tips & Solutions

How to protect a workbook....


Posted by JooBor on August 02, 2001 5:08 AM

Let say I want to protect my workbook with the pw"123"
what is the code.and hope you can give me the full
code coz i'm a novice..thanks


Posted by Alix on August 02, 2001 5:47 AM


Hi

If you go to the File | SaveAs command, there is a button titled Options...
Click this button and it gives you the 'Password to Open' box

HTH

Posted by JooBor on August 02, 2001 7:27 AM

What i meant is to protected all the sheets with 1 password..thanks

Thanks


so the user can only put on data..i know
on how to protect single sheet..but i want
now to protect all sheets in my workbook
with 1 password.i.e "123"

Posted by Joe Was on August 02, 2001 10:49 AM

This was posted by Ryan it does all the sheets.

Here is code that will do what you want. I've been playing with it and it works like a charm. If there are no passwords, then just leave the first inputbox blank, and enter a password in the second one to protect all the sheets. If you want them all unprotected leave the second inputbox blank. Let me know how it works.
Ryan

Sub ChangePassword()
Dim Sheet As Worksheet
Dim OldPass As Variant
Dim NewPass As Variant

Application.ScreenUpdating = False

OldPass = InputBox("Please enter old password", "Old Password")
'If OldPass = "" Then Exit Sub
NewPass = InputBox("Please enter new password", "New Password")

If MsgBox("This will replace your old password with a new one", vbOKCancel, "Continue") _
= vbCancel Then Exit Sub


For Each Sheet In Worksheets
On Error Resume Next
Sheet.Unprotect Password:=OldPass
If NewPass <> "" Then Sheet.Protect Password:=NewPass
Next Sheet

Application.ScreenUpdating = True
End Sub

Let say I want to protect my workbook with the pw"123"

Posted by Joe Was on August 02, 2001 10:59 AM

Tab sheet View Code macro.

This must go into the sheet tab view code module, you connot change the "Private Sub" name or the code will fail! You can change the password. The password is now "admin."

Private Sub Worksheet_Change(ByVal Target As Range)
'
' Macro by Joseph S. Was
'
Application.EnableEvents = False

ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True, Password:="admin"
Application.EnableEvents = True
End Sub


This is a regular module macro to unlock:

Sub myUnLock()
'This code will unprotect the sheet.
Application.EnableEvents = False
Application.DisplayAlerts = False
ActiveSheet.Unprotect ("admin")
Application.EnableEvents = True
Application.DisplayAlerts = True

End Sub

Copy this to Macros-Macro-Create, name = myUnLock

If you changed the password above change it here as well. Exit, then go to Macros-Macro, highlight your unLock macro then click "Options" and assign a hot key. Then if you press Ctrl-u, if "u" was the hot key you selected the sheet will unlock. You can also assign the macro to a screen button. JSW

Let say I want to protect my workbook with the pw"123"

Posted by Robb on August 03, 2001 5:27 AM

Re: What i meant is to protected all the sheets with 1 password..thanks

If you want to run code to protect all sheets in the workbook, just add a code module and type in:

Sub Nameorwhatever()
For each s in ActiveWorkbook.Sheets
s.Protect Password:="123"
Next s
End Sub

Run the routine and all the sheets should be protected. Save the workbook.
To unprotect, run the same code but use Unprotect.

Does this help?

Thanks