problem with protecting sheet

[bernd]

hello,

When selecting a sheet1 the sheet is protected.

Depending on the value (xx) in a cell on sheet2, I want sheet 1 to be
unprotected or stay protected.
I made a reference to the cell on sheet2: status

I created the following code but doesn't work.

Private Sub Worksheet_Activate ()
If Range(status) = "xx" Then
Activesheet.Unprotect Password:= "pass"
Else: Activesheet.Protect Password:= "pass"
End If
End Sub

How do I solve this?

 

[bernd]

I've also tried:

Private Sub Worksheet_Activate ( )
Dim i as variant
i = Status
If i = "xx" Then
Activesheet.Unprotect Password:= "pass"
Else: Activesheet.Protect Password:= "pass"
End If
End Sub

I don't get errors on this code, but it doesn't work. Sheet 1 stays
protected, with the text xx in the cell with reference "status"

 

[Cheetahke]

Hi bernd

You refer to a range on the active sheet, you have to refer to a range on
sheet 2
Try this:

Private Sub Worksheet_Activate ()
If Worksheets(2).Range("status").Value = "xx" Then
Activesheet.Unprotect Password:= "pass"
Else: Activesheet.Protect Password:= "pass"
End If
End Sub