protection puzzle

[B Lynn B]

I posted this yesterday and got a reply that didn't solve anything, so
thought I'd try restating to see if a more revealing answer is available.

For testing, and to simplify the problem, I made a brand new file with no
other code. Rng_A is on an unprotected sheet, and Rng_B on the protected
one. The protection dialog box is checked for allowing user to select locked
cells. Of the examples below, the first works, while the second fails. It
seems to violate the intent of the UserInterfaceOnly setting. Anybody know
what's at the bottom of this?

Example 1:
Sheets(1).unprotect
Range("Rng_A").copy Range("Rng_B")

Example 2:
Sheets(1).protect UserInterfaceOnly:=True
Range("Rng_A").copy Range("Rng_B")

 

[Leith Ross]

I posted this yesterday and got a reply that didn't solve anything, so
thought I'd try restating to see if a more revealing answer i
available.

For testing, and to simplify the problem, I made a brand new file wit
no
other code. Rng_A is on an unprotected sheet, and Rng_B on th
protected
one. The protection dialog box is checked for allowing user to selec
locked
cells. Of the examples below, the first works, while the second fails
It
seems to violate the intent of the UserInterfaceOnly setting. Anybod
know
what's at the bottom of this?

Example 1:
Sheets(1).unprotect
Range("Rng_A").copy Range("Rng_B")

Example 2:
Sheets(1).protect UserInterfaceOnly:=True
Range("Rng_A").copy Range("Rng_B")

Hello B Lynn,

The key here is the word User. It protects the worksheet's content
from being changed by the User but not the Programmer. If you passwor
protect the worksheet as in your first example then it is protected fro
both the user and the programmer

--
Leith Ros

Sincerely,
Leith Ross

'The Code Cage' (http://www.thecodecage.com/

 

[B Lynn B]

Thanks Leith, but I'm not sure the question you're answering is the one I
asked. My goal is to be able to modify the protected sheet through code by
employing the UserInterfaceOnly parameter. And the puzzle is why the second
line of example 2 should throw an error. With this parameter set to True, it
is possible to modify the cells in other ways - just not copying a range.
Why would copying a range not be allowed when other changes are?

My control test was really just to establish that a range which COULD be
copied without protection, would fail when the UserInterfaceOnly protection
was applied.

Any other guesses would be MOST welcome.

 

[Peter T]

There are some quirks with UserInterfaceOnly=True, try it like this

Worksheets(1).Protect UserInterfaceOnly:=True
Range("Rng_A").copy
Range("Rng_B").PasteSpecial xlPasteAll

Don't forget you'll always need to run Protect UserInterfaceOnly:=true after
a wb has been saved and reopened, typically in the open event

Regards,
Peter T

 

[B Lynn B]

Well, I'll be damned! Who would have guessed it would work the way you show,
but not the more direct route with the copy source and destination in the
same statement? But, work it does. I had already gone and written a loop
through each of the cells in the copy range to get the values and needed
formats to the destination. (GRRRR!!!) But I'll go back and replace with
this.

That's got to be one of the more ridiculous things I've seen Excel do.
Thanks so much for clearing up that little mystery!

Lynn