Unprotecting/Reprotecting

[Adam]

I have a VBA code in Module1 of a workbook which protects all the sheets with
the same password. The code is
Sub LockEm()
Dim i As Long
Dim PW As String
Dim WS As Worksheet
PW = InputBox("Enter password to protect all sheets:")
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

Now, I have a macro in the same module in which I would like to unprotect
the sheet with the password entered when protecting all the sheet, execute
some activities and then reprotect it with the same password, i.e. lets say I
i have the macro
Sub xxx()
Range("C4").Select
ActiveCell.FormulaR1C1 = "cc"
End Sub

Where and how do I enter the code?
I assume I need to make PW public or something (I'm quite new at VBA)

Secondly, I would like to apply the same thing as above to a worksheet event
that I have in a specific sheet

Any ideas?
Thanks

 

[K Dales]

You could put the code in Module1 (say we call it UnLockEm) and to allow you
to work with one sheet at a time let's add a parameter for the worksheet
name. Make it a Public Sub so it can be called from any other module. It
appears from your post that the user will not know the password so you would
need to hard code it, which does open it up to anyone with the knowledge of
how to get into VBA, so that is a concern you will have to decide about:

Public Sub UnLockEm(ThisSheetName as String)
With Sheets(ThisSheetName)
.Unprotect("Password")
.Range("C4").FormulaR1C1 = "cc"
.Protect("Password")
End With
End Sub

To link it to your Worksheet event procedures, just make the call as shown
in this example:
Private Sub Worksheet_Change(ByVal Target as Range)
....
UnLockEm(Me.Name)
....
End Sub

 

[Adam]

Thanks,
But is it not possible to for xls to remember the PW and then call it from
another sub.
The procedure will be as follows.
First i run the macro LockEm() and assign a password. Looking at the code
the password is stored in the string PW
Secondly I send out the file with the receiver unknowing of the password
Thirdly the recipient may run a macro. Since the macro contains cells that
are protected it will of course complain.

Therefore I need the macro that the recipient is running to start by
unprotecting the sheet with the password that I assigned (i.e. the string
PW), run the macro and then protect it with same password PW.

I have another macro in the same module as LockEm() called UnlockEm() which
prompts the user to insert the password entered in the macro LockEm(). This I
need to do also for the other macro but I do not know how to get the macro to
recognize the PW parameter. I tried Worksheets("Sheet1").Unprotect(PW) in the
beginning of the macro but it did not recognized the parameter
/Adam

"K Dales" skrev:

 

[Dean J.]

You can store worksheet-specific data in "Custom Properties", that as far as
I can tell, doesn't show up anywhere, so it should be safe for passwords.

public sub LockEm()
dim PW as string
dim objSheet as worksheet

PW = InputBox("Enter password to protect all sheets:")
for each objSheet in sheets
objSheet.Protect PW
objSheet.CustomProperties.Add "PassCode", PW
next
end sub

public sub ChangeSheet(byval strSheet as string)
dim PassCode as string

with sheets(strSheet)
PassCode = .CustomProperties(1).value ' can access it by index only
.Unprotect PassCode
' do some changes
.Protect PassCode
end with
end sub

There is a typical problem here though with lame Microsoft documentation on
this topic. I can't find where these properties are stored. Using something
that you don't know much about...well...use @ your own risk.