Select statement or me?

[tony]

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.

 

[Ron Rosenfeld]

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

 

[ozgrid.com]

What sort of reference is "$y$n" and Range("$y$n")???

 

[ozgrid.com]

Does this help?


Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Target.Address = "X$1" Then
With Sheets("mysheet")
.Protect Password:="mypassword"
With .Range("Y1")
'.With code here
End With
End With
End If
End Sub

 

[Dave Peterson]

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]

Does this help?

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Cells.Count > 1 Then Exit Sub
        If Target.Address = "X$1" Then
           With Sheets("mysheet")
               .Protect Password:="mypassword"
                    With .Range("Y1")
                       '.With code here
                    End With
           End With
        End If
End Sub

--
Regards





- Show quoted text -

In response to Rod, I am using a spreadsheet to collect data from our
suppliers and would prefer them not to be abe to 'fiddle' with the
spreadsheet. Thus all cells expect those I want them to be able to
enter data in are locked. Sometimes this requires a locked cell to
become unlocked and sometimes the other way round. As we all know in
order for a cell to be on any use when locked you have to password
protect the sheet.

In response to Dave, the reference I used is purely hyperthetical, it
could be $A$4 or a range $A$4:$G23, it was just for illustration
purposes. Also thanks for the suggested code which I can see would do
the job as this is how I got round the problem. The question was why
the bizzare running order of the sub, surely the protect action should
take place AFTER the locked action, when it would appear to occur
BEFORE?

Weird huh?

 

[tony]

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.











--

Dave Peterson- Hide quoted text -

- Show quoted text -

Ah! So you're saying that if I want to reference the sheet currently
being worked in I should use the Me. identifyer, as the other way may
confuse the issue. What if - as in this case - the referenced sheet is
the same sheet as that being worked upon. I would have thought this
would have the same result?

Also please accept my apologies for the random words in the last post,
for 'expect' read 'except' and 'cell to be on' should read 'cell to be
of'.

 

[Dave Peterson]

It's always a good idea to qualify your ranges and objects. You'll never have
to worry about simple changes (like copying from one module to another) breaking
your code.

If the ranges/objects belong to the sheet that owns the code, you can use the Me
keyword. I wouldn't refer to the worksheet name or even the codename. Those
can change and break the code.

In fact, if you're like me, you may find that you copy|paste from working
procedures and reuse the code in other locations. Using the Me keyword and
pasting into a worksheet module will keep the code as-is.

If you use the with/end with structure, like:
with worksheets("somesheetnamehere")

You'll only have to modify the code in that one spot.

In fact, I'd use:

Dim wks as worksheet
....
set wks = worksheets("somesheetnamehere")
....
with wks
...
end with

It would make it even easier to modify.




tony wrote:

 

[Project Mangler]

Tony,

If the sheet is protected with Worksheets("Sheet1").Protect Password:="abc"
then code like Worksheets("Sheet1").Range("A6").Locked = True gives an
error.

If it is protected with Worksheets("Sheet1").Protect Password:="abc",
Contents:=True, UserInterfaceOnly:=True then there is no error.

Or am I missing the point?

 

[tony]

It's always a good idea to qualify your ranges and objects.  You'll never have
to worry about simple changes (like copying from one module to another) breaking
your code.

If the ranges/objects belong to the sheet that owns the code, you can usethe Me
keyword.  I wouldn't refer to the worksheet name or even the codename.  Those
can change and break the code.

In fact, if you're like me, you may find that you copy|paste from working
procedures and reuse the code in other locations.  Using the Me keywordand
pasting into a worksheet module will keep the code as-is.

If you use the with/end with structure, like:
   with worksheets("somesheetnamehere")

You'll only have to modify the code in that one spot.

In fact, I'd use:

Dim wks as worksheet
...
set wks = worksheets("somesheetnamehere")
...
with wks
    ...
end with

It would make it even easier to modify.

tony wrote:

<<snipped>>

Many thanks Dave, I'm not a great user of VBA in spreadsheets as I
usually use Access for most of my tasks but this is most helpful. I
think I will need to do some homework on Excel coding.

 

[tony]

Tony,

If the sheet is protected with Worksheets("Sheet1").Protect Password:="abc"
then code like Worksheets("Sheet1").Range("A6").Locked = True gives an
error.

If it is protected with Worksheets("Sheet1").Protect Password:="abc",
Contents:=True, UserInterfaceOnly:=True then there is no error.

Or am I missing the point?











- Show quoted text -

That's probably why I get an error when attempting to change the cell
to .locked = true when the sheet was password protected, so I'll give
that a bash. However it doesn't solve the strange running order of the
sub. If you look at the original post I say that the protect statement
appears to run before the case command has completed and that can't be
correct. Give it a try and when the sub errors you will notice the
worksheet is protected when it just shouldn't be.

 

[Project Mangler]

Hi Tony,

I tried the code from your first post and it caused a lot of screen flicker
which made me think that the change event was being triggered over and over
until it stopped with an error on the lock code.

Have you tried disabling events in the code and resetting them on exit?

Tony,

If the sheet is protected with Worksheets("Sheet1").Protect Password:="abc"
then code like Worksheets("Sheet1").Range("A6").Locked = True gives an
error.

If it is protected with Worksheets("Sheet1").Protect Password:="abc",
Contents:=True, UserInterfaceOnly:=True then there is no error.

Or am I missing the point?











- Show quoted text -

That's probably why I get an error when attempting to change the cell
to .locked = true when the sheet was password protected, so I'll give
that a bash. However it doesn't solve the strange running order of the
sub. If you look at the original post I say that the protect statement
appears to run before the case command has completed and that can't be
correct. Give it a try and when the sub errors you will notice the
worksheet is protected when it just shouldn't be.

 

[Zeq]

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 origina
"bizarre execution order" has not been fully discussed yet. To me thi
sounds like the changes you do after unlocking Range("$y$n") result i
this sub running again before the current sub gets to locking thi
Range.

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

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

 

[tony]

tony;715598 Wrote:

Can someone explain the bizzare behaviour I am getting with the











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/showthread.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!