On Tue, 4 May 2010 03:22:20 -0700 (PDT), 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.

Perhaps you don't need to unlock/lock the cell. Doesn't that only have an
effect if the sheet is Protected? If that is the case, UNprotecting the sheet
should be all that is necessary.
--ron