When you have unqualified ranges in a General module, they refer to the
activesheet.

But when you have unqualified ranges in a worksheet module, then they refer to
the sheet that owns the code.

If mySheet is the sheet with the code:

Private Sub Worksheet_Change(ByVal Target As Range)
me.Unprotect "mypassword"
Select Case Target.Address
Case $x$n
me.Range("$y$n").Locked = False
'perform some action to cell $y$n
me.Range($y$n").Locked = True
End Select
me.Protect "mypassword"
End Sub

If mySheet is a different sheet:

Private Sub Worksheet_Change(ByVal Target As Range)
with Sheets("mysheet")
.Unprotect "mypassword"
Select Case Target.Address
Case $x$n
.Range("$y$n").Locked = False
'perform some action to cell $y$n
.Range($y$n").Locked = True
End Select
.Protect "mypassword"
end with
End Sub

Those leading dots mean that the next object/property belongs to the object in
the previous with statement. In this case, it's the mySheet worksheet.
tony wrote:

Can someone explain the bizzare behaviour I am getting with the
following select statement?

Private Sub Worksheet_Change(ByVal Target As Range)
Sheets("mysheet").Select
ActiveSheet.Unprotect ("mypassword")
Select Case Target.Address
Case $x$n
Range("$y$n").Select
Range("$y$n").Locked = False
'perform some action to cell $y$n
Range($y$n").Locked = True
End Select
ActiveSheet.Protect("mypassword")
End Sub

You would expect that the sub would unprotect the worksheet, select
the cell, unlock the cell, perform the action ,lock the cell and
protect the worksheet.

What appears to happen is this:
The sub unprotects the worksheet, selects the cell, unlocks the cell,
performs the action , protects the worksheet and then attempts to lock
the cell (which it can't because the worksheet is now protected).

I've managed to work around this by putting the protect bit in the
case statement but surely this isn't right is it?

Just in case your wondering I'm not referencing merged cells - I
thought that was the problem but is isn't.

--

Dave Peterson