Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 - |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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!!! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Protect Workbook Vs Protect Sheet | New Users to Excel | |||
Can I protect columns w/in a "List" using Protect Sheet? | Excel Discussion (Misc queries) | |||
Interrupted sheet copying causes phantom Book2 | Excel Programming | |||
Lock and protect cells without protect the sheet | Excel Programming | |||
excel - macro code to open a protected sheet, enter passowrd, and then protect sheet | Excel Programming |