Use a password in VBA ActiveSheet.protect & ActiveSheet.unprotect?
 

I have about a dozen worksheets in a workbook that are password protected. I
want to use a macro to unprotect each sheet (one at a time), lock or unlock
some cells, and then password protect the worksheet before moving to the next
sheet. The password is the same for all the worksheets. I would like the
macro to prompt one time for the password and then use that input to perform
the unprotect and protect. I got the VBA code below when I recorded a macro.
Unfortunately, using this code, the user is prompted each time the worksheet
is unprotected, the cell locks are set, and then the worksheet is protected
again but without the password.

Sheets("Special Loans").Select
ActiveSheet.Unprotect
Cells.Select
Range("M1").Activate
Selection.Locked = True
Selection.FormulaHidden = False
Range("A1").Select
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
ActiveWorkbook.Save

Use a password in VBA ActiveSheet.protect & ActiveSheet.unprotect?
 

Hi Jim,

Try something like:

'===========
Public Sub Tester()
Dim WB As Workbook
Dim SH As Worksheet
Dim PWORD As String

PWORD = InputBox(Prompt:="Insert password")

Set WB = ThisWorkbook

For Each SH In WB.Worksheets
With SH
On Error GoTo XIT
.Unprotect PWORD
With .Range("M1")
.Locked = True
.FormulaHidden = False
End With
.Protect password:=PWORD, _
DrawingObjects:=True, _
Contents:=True, _
Scenarios:=True
End With
Next SH
WB.Save
Exit Sub

XIT:
MsgBox Prompt:="Password not recognised"
End Sub
'<<===========



---
Regards.
Norman


"Jim K." <Jim wrote in message
...
I have about a dozen worksheets in a workbook that are password protected.
I
want to use a macro to unprotect each sheet (one at a time), lock or
unlock
some cells, and then password protect the worksheet before moving to the
next
sheet. The password is the same for all the worksheets. I would like the
macro to prompt one time for the password and then use that input to
perform
the unprotect and protect. I got the VBA code below when I recorded a
macro.
Unfortunately, using this code, the user is prompted each time the
worksheet
is unprotected, the cell locks are set, and then the worksheet is
protected
again but without the password.

Sheets("Special Loans").Select
ActiveSheet.Unprotect
Cells.Select
Range("M1").Activate
Selection.Locked = True
Selection.FormulaHidden = False
Range("A1").Select
ActiveSheet.Protect DrawingObjects:=True, Contents:=True,
Scenarios:=True
ActiveWorkbook.Save

Use a password in VBA ActiveSheet.protect & ActiveSheet.unprot
 

That was just the code I was looking for. THANKS!!!

"Norman Jones" wrote:

Hi Jim,

Try something like:

'===========
Public Sub Tester()
Dim WB As Workbook
Dim SH As Worksheet
Dim PWORD As String

PWORD = InputBox(Prompt:="Insert password")

Set WB = ThisWorkbook

For Each SH In WB.Worksheets
With SH
On Error GoTo XIT
.Unprotect PWORD
With .Range("M1")
.Locked = True
.FormulaHidden = False
End With
.Protect password:=PWORD, _
DrawingObjects:=True, _
Contents:=True, _
Scenarios:=True
End With
Next SH
WB.Save
Exit Sub

XIT:
MsgBox Prompt:="Password not recognised"
End Sub
'<<===========



---
Regards.
Norman


"Jim K." <Jim wrote in message
...
I have about a dozen worksheets in a workbook that are password protected.
I
want to use a macro to unprotect each sheet (one at a time), lock or
unlock
some cells, and then password protect the worksheet before moving to the
next
sheet. The password is the same for all the worksheets. I would like the
macro to prompt one time for the password and then use that input to
perform
the unprotect and protect. I got the VBA code below when I recorded a
macro.
Unfortunately, using this code, the user is prompted each time the
worksheet
is unprotected, the cell locks are set, and then the worksheet is
protected
again but without the password.

Sheets("Special Loans").Select
ActiveSheet.Unprotect
Cells.Select
Range("M1").Activate
Selection.Locked = True
Selection.FormulaHidden = False
Range("A1").Select
ActiveSheet.Protect DrawingObjects:=True, Contents:=True,
Scenarios:=True
ActiveWorkbook.Save