Phantom sheet protect

[Dave Unger]

Hello everyone,

Has anyone else run into this one?

With Sheet1 active, I run the following bit of code:

Sheet2.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
Sheet2.EnableSelection = xlUnlockedCells

Besides Sheet2 getting protected, Sheet1 also seems to get a
“phantom” protection, and I can’t select any of its locked cells. If
I persist in trying, Excel locks up, and goes into never never land,
and I need to use Ctrl Alt Del to break out of it.

I get no indication that Sheet1 is actually protected, and un-
selecting, then re-selecting Sheet1 puts everything right. Also, if
Sheet2 is active while running the procedure, all is well

If I run this under XL97, I have no problem whatsoever, only with
XL2007. I’m wondering if any other later than XL97 versions are also
experiencing this.

I’ve un-installed/re-installed XL2007 to no avail, and so far have
been unable to find any mention of this in the groups, Microsoft,
etc. Any light that someone can shed on this is most appreciated.

Regards,

DaveU

 

[Barb Reinhardt]

Why not build in a way to do this in the code

Dim aWS as Worksheet

set aWS = ActiveSheet

Sheet2.Select

'do your stuf

aWS.select

 

[Dave]

Hi Barb,

Thanks for your reply.

I am already using a similar workaround - I was just wondering if
anyone else has run into this, or is it just me

regards,

DaveU

 

[Dave]

Hi Simon,

The code I've posted is all there is to it (I dummied this down from a
very large application). So, just generate a new workbook, and insert
this sub in a regular module. That's it.

Sub Test()

Sheet2.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
Sheet2.EnableSelection = xlUnlockedCells

End Sub

Thanks for your interest.

Regards,

DaveU

 

[Dave]

Hi Simon,

Thanks for confirming that for me. I'm copying your reply here for
the benifit of anyone else who is following this thread.

regards,

DaveU


Hi Dave i tried your code and i tried qualifying the sheet too, both
worked fine for the first protect but subsequent protects caused sheet
one to have its last activecell selection locked but allowing you to
select and edit a further cell, running protection on sheet 2 again
caused excel to freeze as you said, so the only way i can see is to
unprotect sheet 2 first then reprotect it.....its definitly a quirk
you have found, MS are due to release SP2 for Office 2007 some
problems with 2007 will be fixed some not even addressed, however they
are working on the next version of excel too so maybe they have fixed
the errors and built it with us in mind!!!