Unprotecting and reprotecting within a macro
 

I have these codes which protect all my sheets in a workbook with the same
password

Sub LockEm()
Dim i As Long
Dim PW As String
Dim WS As Worksheet
PW = InputBox("Password:")
On Error GoTo MyErr
For Each WS In ActiveWorkbook.Worksheets
WS.Protect (PW)
Next
MsgBox i & " errors while protecting", vbInformation
Exit Sub
MyErr:
i = i + 1
Resume Next
End Sub

Sub UnLockEm()
Dim i As Long
Dim PW As String
Dim WS As Worksheet
PW = InputBox("Password:")
On Error GoTo MyErr
For Each WS In ActiveWorkbook.Worksheets
WS.Unprotect (PW)
Next
MsgBox i & " errors while unprotecting", vbInformation
Exit Sub
MyErr:
i = i + 1
Resume Next
End Sub

I wonder if it is possible to do the following:
1. First I Protect the sheets with the code in LockEm() and the password
qwerty
2. Then I have a macro called Macro1(). I cannot run this macro without
unprotecting the sheet since the macro refers to protected cells
Therefore I would add code to the Macro1() that in the beginning of the
sequence unprotect the sheet with the password entered above in the code
LockEm() i.e. PW. In this case the password is qwerty but that may differ
from time to time so therefore I cannot simply use unprotect("qwerty").
When I tried to enter the code unprotect(PW) VB did not recognize the
parameter PW. FYI the codes are in the same module.

Howcome the macro1() cannot recognize the PW but the code UnlockEm() can
(since it complains if I do not type in the same password as I did when I
protected the sheets)?
Is it at all possible to have a macro like:

Sub Macro1()
xxx.unprotect(PW)
' some code
xxx.protect(PW)
End sub

I have gotten some good hints but non of them has really solved my problem
Any help is deeply appreciated

Unprotecting and reprotecting within a macro
 

Sub LockEmWithPW(PW as String)
Dim i As Long
Dim WS As Worksheet
On Error GoTo MyErr
For Each WS In ActiveWorkbook.Worksheets
WS.Protect PW
Next
MsgBox i & " errors while protecting", vbInformation
Exit Sub
MyErr:
i = i + 1
Resume Next
End Sub

Sub UnLockEmWithPW(PW as String)
Dim i As Long
Dim WS As Worksheet
On Error GoTo MyErr
For Each WS In ActiveWorkbook.Worksheets
WS.Unprotect PW
Next
MsgBox i & " errors while unprotecting", vbInformation
Exit Sub
MyErr:
i = i + 1
Resume Next
End Sub

Sub Macro1()
Dim PW As String
PW = InputBox("Password:")
If PW = "" then Exit Sub 'User cancelled?
UnLockEmWithPW PW

On error goto LockUp
'Do Something

LockUp:
LockEmWithPW PW
End Sub

Regards - Steve

PS, this wont work for blank passwords (If it must, let me know & I'll show you how to handle that)

"Adam" wrote in message ...
I have these codes which protect all my sheets in a workbook with the same
password

Sub LockEm()
Dim i As Long
Dim PW As String
Dim WS As Worksheet
PW = InputBox("Password:")
On Error GoTo MyErr
For Each WS In ActiveWorkbook.Worksheets
WS.Protect (PW)
Next
MsgBox i & " errors while protecting", vbInformation
Exit Sub
MyErr:
i = i + 1
Resume Next
End Sub

Sub UnLockEm()
Dim i As Long
Dim PW As String
Dim WS As Worksheet
PW = InputBox("Password:")
On Error GoTo MyErr
For Each WS In ActiveWorkbook.Worksheets
WS.Unprotect (PW)
Next
MsgBox i & " errors while unprotecting", vbInformation
Exit Sub
MyErr:
i = i + 1
Resume Next
End Sub

I wonder if it is possible to do the following:
1. First I Protect the sheets with the code in LockEm() and the password
qwerty
2. Then I have a macro called Macro1(). I cannot run this macro without
unprotecting the sheet since the macro refers to protected cells
Therefore I would add code to the Macro1() that in the beginning of the
sequence unprotect the sheet with the password entered above in the code
LockEm() i.e. PW. In this case the password is qwerty but that may differ
from time to time so therefore I cannot simply use unprotect("qwerty").
When I tried to enter the code unprotect(PW) VB did not recognize the
parameter PW. FYI the codes are in the same module.

Howcome the macro1() cannot recognize the PW but the code UnlockEm() can
(since it complains if I do not type in the same password as I did when I
protected the sheets)?
Is it at all possible to have a macro like:

Sub Macro1()
xxx.unprotect(PW)
' some code
xxx.protect(PW)
End sub

I have gotten some good hints but non of them has really solved my problem
Any help is deeply appreciated

Unprotecting and reprotecting within a macro
 

Thanks Stevie,
Only one problem, the executor of the macro will not know the password,
therefore I need the macro to call the PW that I entered and automatically
use it in Macro1()

"Stevie_mac" skrev:

Sub LockEmWithPW(PW as String)
Dim i As Long
Dim WS As Worksheet
On Error GoTo MyErr
For Each WS In ActiveWorkbook.Worksheets
WS.Protect PW
Next
MsgBox i & " errors while protecting", vbInformation
Exit Sub
MyErr:
i = i + 1
Resume Next
End Sub

Sub UnLockEmWithPW(PW as String)
Dim i As Long
Dim WS As Worksheet
On Error GoTo MyErr
For Each WS In ActiveWorkbook.Worksheets
WS.Unprotect PW
Next
MsgBox i & " errors while unprotecting", vbInformation
Exit Sub
MyErr:
i = i + 1
Resume Next
End Sub

Sub Macro1()
Dim PW As String
PW = InputBox("Password:")
If PW = "" then Exit Sub 'User cancelled?
UnLockEmWithPW PW

On error goto LockUp
'Do Something

LockUp:
LockEmWithPW PW
End Sub

Regards - Steve

PS, this wont work for blank passwords (If it must, let me know & I'll show you how to handle that)

"Adam" wrote in message ...
I have these codes which protect all my sheets in a workbook with the same
password

Sub LockEm()
Dim i As Long
Dim PW As String
Dim WS As Worksheet
PW = InputBox("Password:")
On Error GoTo MyErr
For Each WS In ActiveWorkbook.Worksheets
WS.Protect (PW)
Next
MsgBox i & " errors while protecting", vbInformation
Exit Sub
MyErr:
i = i + 1
Resume Next
End Sub

Sub UnLockEm()
Dim i As Long
Dim PW As String
Dim WS As Worksheet
PW = InputBox("Password:")
On Error GoTo MyErr
For Each WS In ActiveWorkbook.Worksheets
WS.Unprotect (PW)
Next
MsgBox i & " errors while unprotecting", vbInformation
Exit Sub
MyErr:
i = i + 1
Resume Next
End Sub

I wonder if it is possible to do the following:
1. First I Protect the sheets with the code in LockEm() and the password
qwerty
2. Then I have a macro called Macro1(). I cannot run this macro without
unprotecting the sheet since the macro refers to protected cells
Therefore I would add code to the Macro1() that in the beginning of the
sequence unprotect the sheet with the password entered above in the code
LockEm() i.e. PW. In this case the password is qwerty but that may differ
from time to time so therefore I cannot simply use unprotect("qwerty").
When I tried to enter the code unprotect(PW) VB did not recognize the
parameter PW. FYI the codes are in the same module.

Howcome the macro1() cannot recognize the PW but the code UnlockEm() can
(since it complains if I do not type in the same password as I did when I
protected the sheets)?
Is it at all possible to have a macro like:

Sub Macro1()
xxx.unprotect(PW)
' some code
xxx.protect(PW)
End sub

I have gotten some good hints but non of them has really solved my problem
Any help is deeply appreciated