protection puzzle
 

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")

protection puzzle
 

B Lynn B;477645 Wrote:
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")

Hello B Lynn,

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


--
Leith Ross

Sincerely,
Leith Ross

'The Code Cage' (http://www.thecodecage.com/)
------------------------------------------------------------------------
Leith Ross's Profile: http://www.thecodecage.com/forumz/member.php?userid=75
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=131779

protection puzzle
 

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.


"Leith Ross" wrote:


B Lynn B;477645 Wrote:
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")

Hello B Lynn,

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


--
Leith Ross

Sincerely,
Leith Ross

'The Code Cage' (http://www.thecodecage.com/)
------------------------------------------------------------------------
Leith Ross's Profile: http://www.thecodecage.com/forumz/member.php?userid=75
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=131779

protection puzzle
 

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" wrote in message
...
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")

protection puzzle
 

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

"Peter T" wrote:

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" wrote in message
...
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")