Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Shapes and protection xl2000/2003
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Shapes and protection xl2000/2003
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 "Dave Unger" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Shapes and protection xl2000/2003
OOps. Better qualify the shapes:
For i = Sheet2.Shapes.Count To 1 Step -1 Sheet2.Shapes(i).Delete Next "Dave Unger" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Shapes and protection xl2000/2003
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Shapes and protection xl2000/2003
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") "Dave Unger" wrote in message ... 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Shapes and protection xl2000/2003
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel 2003 - Shapes | Excel Programming | |||
Shapes in 2007 from 2003 | Excel Discussion (Misc queries) | |||
XL2000/2003 Compatibility Issue | Excel Programming | |||
COM interop XL2000 vs 2003 | Excel Programming | |||
ColorIndex property XL2000 vs 2003 | Excel Programming |