Phantom sheet protect
 

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

Phantom sheet protect
 

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 Unger" wrote:

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 cant 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. Im wondering if any other later than XL97 versions are also
experiencing this.

Ive 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

Phantom sheet protect
 

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

On Apr 11, 4:13*pm, Barb Reinhardt
wrote:
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

Phantom sheet protect
 

Can you post the workbook as i would like to see this?

Dave;304830 Wrote:
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

On Apr 11, 4:13*pm, Barb Reinhardt
wrote:
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




--
Simon Lloyd

Regards,
Simon Lloyd
'The Code Cage' (http://www.thecodecage.com)
------------------------------------------------------------------------
Simon Lloyd's Profile: http://www.thecodecage.com/forumz/member.php?userid=1
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=85261

Phantom sheet protect
 

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







On Apr 11, 5:54*pm, Simon Lloyd
wrote:
Can you post the workbook as i would like to see this?

Dave;304830 Wrote:





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

On Apr 11, 4:13*pm, Barb Reinhardt
wrote:
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

--
Simon Lloyd

Regards,
Simon Lloyd
'The Code Cage' (http://www.thecodecage.com)
------------------------------------------------------------------------
Simon Lloyd's Profile:http://www.thecodecage.com/forumz/member.php?userid=1
View this thread:http://www.thecodecage.com/forumz/sh...d.php?t=85261- Hide quoted text -

- Show quoted text -

Phantom sheet protect
 

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!!!