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:

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" wrote:

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