Password protected sheets

[Francis Hookam]

A friend needs to protect a 20 sheet worksheet ­ it seems to me that all
cells in each sheet need to be locked and then individual sheets protected
something like the unrefined and quickly recorded macro below

However, although I entered a password when protecting the sheet, none shows
up in the macro. I realise that someone could see the password within the
macro but the macro can be protected

Can you show me how to protect each sheet WITH a password? Thanks

Francis Hookham

Sub LockUp()
Count = 10
While Count > 0
Sheets("Sheet" & Count).Select
Cells.Select
Selection.Locked = True
Selection.FormulaHidden = True
Range("A1").Select
ActiveSheet.Protect DrawingObjects:=True, Contents:=True,
Scenarios:=True
ActiveSheet.EnableSelection = xlNoSelection
Count = Count - 1
Wend
End Sub

 

[J Laroche]

Hi Francis,

Here are two procedures to protect and unprotect all sheets in a workbook.
Define MyPassword somewhere in the constants declaration block (preferred
method), or write the password directly in the procedures (in quotes).

JL
Mac OS X 10.3.7, Office v.X 10.1.6



Sub ProtectAll(wb)
' wb is the workbook to protect, remove argument if unnecessary

Dim ws As Worksheet

For Each ws In wb.Worksheets ' or use ThisWorkbook.Worksheets
ws.Protect Password:=MyPassword, Contents:=True, _
DrawingObjects:=True
Next

End Sub

Sub UnprotectAll(wb)
' wb is the workbook to unprotect, remove argument if unnecessary

Dim ws As Worksheet

For Each ws In wb.Worksheets ' or use ThisWorkbook.Worksheets
ws.Unprotect Password:=MyPassword
Next

End Sub





Francis Hookam wrote on 2005/01/23 16:05:

 

[JE McGimpsey]

Here are two procedures to protect and unprotect all sheets in a workbook.
Define MyPassword somewhere in the constants declaration block (preferred
method), or write the password directly in the procedures (in quotes).

Just a couple of tweaks, to use optional arguments, rather than
rewriting the routines (I have routines in my global templates set up
this way):

Public Sub ProtectAll(Optional wb As Workbook, _
Optional pWord As String = "")
Dim ws As Worksheet
If wb Is Nothing Then Set wb = ActiveWorkbook
For Each ws In wb.Worksheets
ws.Protect _
Password:=pWord, _
DrawingObjects:=True, _
Contents:=True, _
Scenarios:=True, _
UserInterfaceOnly:=True
Next ws
End Sub


Public Sub UnprotectAll(Optional wb As Workbook, _
Optional pWord As String = "")
Dim ws As Worksheet
If wb Is Nothing Then Set wb = ActiveWorkbook
For Each ws In wb.Worksheets
ws.Unprotect Password:=pWord
Next ws
End Sub


One could either change the default passwords from "" to "drowssap" (or
whatever), or call the routines from a second macro:

Public Sub ToggleProtection()
Const sPWORD As String = "drowssap"
If ActiveWorkbook.Worksheets(1).ProtectContents Then
ProtectAll pWord:=sPWORD
Else
UnprotectAll pWord:=sPWORD
End If
End Sub