On 5 May, 07:17, Zeq wrote:
tony;715598 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.

A lot of good comments to this post already, but I suppose the original
"bizarre execution order" has not been fully discussed yet. To me this
sounds like the changes you do after unlocking Range("$y$n") result in
this sub running again before the current sub gets to locking this
Range.

This could happen e.g. if you have this Worksheet_Change sub in sheet
"mysheet". What then would happen is that the second instance of this
sub, activated by your changes and running in the middle of the first
instance, protects the sheet before the first instance continues and
tries to lock the range. I realise the above is not as clear as it could
be, hope you get my point....

Should this be the case - I suggest testing by setting breakpoints in
your code - you might want to have Application.EnableEvents=False at the
beginning of your sub and Application.EnableEvents=True at the end to
prevent it running again if the sub itself makes changes.

--
Zeq
------------------------------------------------------------------------
Zeq's Profile:http://www.thecodecage.com/forumz/member.php?u=1626
View this thread:http://www.thecodecage.com/forumz/sh...d.php?t=200317

http://www.thecodecage.com/forumz- Hide quoted text -

- Show quoted text -

Hi Zeq,

That's what I suspected so I did use the Application.EnableEvents and
still no joy, but I think it has to be a case of the sub running twice
- actually it's the only explanation.

Anyway thanks to everyone for their contributions, I did eventually
get my code to do what I wanted even though it became a bit long
winded. I just have to test it out on my work mates now and see if
they can break it!

They usually do!