Shapes and protection xl2000/2003

[Dave Unger]

Hello everyone,

I’ve tried searching the various groups, but I haven’t come across
anything that quite addresses this.

I have the following code, dummied down from a large application.
Basically, running the code with Sheet1 active, deleting a shape(s)
from Sheet2.

Sub deleteShape()

Dim Shp As Shape

Sheet1.Protect , True, True, True, True
Sheet2.Protect , True, True, True, True

' Sheet1.Protect , False
Sheet2.Protect , False

For Each Shp In Sheet2.Shapes
Shp.Delete
Next Shp

End Sub

As it stands, the code works as expected with xl2007. However, it
will kick out a 1004 error running under xl2000 or 2003 at the
Shp.Delete line. I can get it to work by either uncommenting the line
“Sheet1.Protect, False”, or, by keeping Sheet2 active. Also runs ok
if Sheet3 is active (unprotected sheet). In other words, it seems as
if the active sheet has to be unprotected, in addition to sheet2.

If someone could confirm that this is correct, I would be most
obliged. I ran into this at the last minute, (application developed
in xl2007), expected to do a few quick tests with xl2003 and be done
with it. Well, 3 hours later . . .;-)

Regards,

DaveU

 

[JLGWhiz]

Change this:

For Each Shp In Sheet2.Shapes
Shp.Delete
Next Shp

To this:

For i = Sheet2.Shapes.Count To 1 Step -1
Shapes(i).Delete
Next





Hello everyone,

I’ve tried searching the various groups, but I haven’t come across
anything that quite addresses this.

I have the following code, dummied down from a large application.
Basically, running the code with Sheet1 active, deleting a shape(s)
from Sheet2.

Sub deleteShape()

Dim Shp As Shape

Sheet1.Protect , True, True, True, True
Sheet2.Protect , True, True, True, True

' Sheet1.Protect , False
Sheet2.Protect , False

For Each Shp In Sheet2.Shapes
Shp.Delete
Next Shp

End Sub

As it stands, the code works as expected with xl2007. However, it
will kick out a 1004 error running under xl2000 or 2003 at the
Shp.Delete line. I can get it to work by either uncommenting the line
“Sheet1.Protect, False”, or, by keeping Sheet2 active. Also runs ok
if Sheet3 is active (unprotected sheet). In other words, it seems as
if the active sheet has to be unprotected, in addition to sheet2.

If someone could confirm that this is correct, I would be most
obliged. I ran into this at the last minute, (application developed
in xl2007), expected to do a few quick tests with xl2003 and be done
with it. Well, 3 hours later . . .;-)

Regards,

DaveU

 

[JLGWhiz]

OOps. Better qualify the shapes:

For i = Sheet2.Shapes.Count To 1 Step -1
Sheet2.Shapes(i).Delete
Next





Hello everyone,

I’ve tried searching the various groups, but I haven’t come across
anything that quite addresses this.

I have the following code, dummied down from a large application.
Basically, running the code with Sheet1 active, deleting a shape(s)
from Sheet2.

Sub deleteShape()

Dim Shp As Shape

Sheet1.Protect , True, True, True, True
Sheet2.Protect , True, True, True, True

' Sheet1.Protect , False
Sheet2.Protect , False

For Each Shp In Sheet2.Shapes
Shp.Delete
Next Shp

End Sub

As it stands, the code works as expected with xl2007. However, it
will kick out a 1004 error running under xl2000 or 2003 at the
Shp.Delete line. I can get it to work by either uncommenting the line
“Sheet1.Protect, False”, or, by keeping Sheet2 active. Also runs ok
if Sheet3 is active (unprotected sheet). In other words, it seems as
if the active sheet has to be unprotected, in addition to sheet2.

If someone could confirm that this is correct, I would be most
obliged. I ran into this at the last minute, (application developed
in xl2007), expected to do a few quick tests with xl2003 and be done
with it. Well, 3 hours later . . .;-)

Regards,

DaveU

 

[Dave Unger]

Hi JLGWhiz

Thanks for your reply.

OOps.  Better qualify the shapes:

For i = Sheet2.Shapes.Count To 1 Step -1
     Sheet2.Shapes(i).Delete
Next

No, that did not fix the problem. Just to re-phrase my scenario -
sheet1 is the active sheet, and is protected, the shape is on sheet2,
and sheet2 is un-protected. I get a 1004 error every time, both on
xl2000 & 2003, on different machines. Set it up that way and see if
it fails for you.

regards,

DaveU

 

[JLGWhiz]

Sure enough, if a sheet is protected it produces the error even though the
action is on an unprotected sheet. The way around that is to:

Sheets(1).Unprotect ("Password")
For i = Sheet2.Shapes.Count To 1 Step -1
Sheet2.Shapes(i).Delete
Next
Sheets(1).Protect ("Password")



Hi JLGWhiz

Thanks for your reply.

OOps. Better qualify the shapes:

For i = Sheet2.Shapes.Count To 1 Step -1
Sheet2.Shapes(i).Delete
Next

No, that did not fix the problem. Just to re-phrase my scenario -
sheet1 is the active sheet, and is protected, the shape is on sheet2,
and sheet2 is un-protected. I get a 1004 error every time, both on
xl2000 & 2003, on different machines. Set it up that way and see if
it fails for you.

regards,

DaveU

 

[Dave Unger]

Hi JLGWhiz

Sure enough, if a sheet is protected it produces the error even though the
action is on an unprotected sheet. The way around that is to:

Sheets(1).Unprotect ("Password")
For i = Sheet2.Shapes.Count To 1 Step -1
Sheet2.Shapes(i).Delete
Next
Sheets(1).Protect ("Password")

That's right, exactly what I did, more or less, in my original post,
by un-commenting line "Sheet1.Protect , False". I just wanted to
confirm that this was "the way it is, and we have to live with it", as
it seems like an anomoly to me. Especially when xl2007 works as one
would expect (at least, in this instance). Thanks for your input.

regards,

DaveU