Difference between Activesheet.unprotect

[Guest]

Using XL 2003


Can someone explain the differences between:

(A) ActiveSheet.Protect UserInterfaceOnly:=True
vs.
(B) ActiveSheet.Unprotect

In most cases where I have protected sheets, I can use (A) with no problems.

When attempting to use a macro to first delete then re-establish data
validation with different parameters (in a selected Range), I ran into a type
of " lost contact with X or Y error".

If I manually unprotected the sheet then ran the macro all was OK.

What I found was

Test Sub1()
ActiveSheet.Unprotect

..
.. "Data validation via VBA code"
..
..
..
..

ActiveSheet.Protect ' works
End Sub


Test Sub2()
ActiveSheet.Protect UserInterfaceOnly:=True
..
..
.. "Data validation via VBA code"
..
..
.. ' did not always work as mostly received
' error message mentioned above
End Sub

Any thoughts as to why?

Dennis

 

[Jim Rech]

ActiveSheet.Unprotect allows users and macros free rein to make any kind of
change to a worksheet.

ActiveSheet.Protect UserInterfaceOnly:=True prevents users from making
changes to Locked cells directly through the UI, yet allows macros to make
(some of) those changes. This is mainly a convenience to developers so
everything they do via macros does not have to involve unprotecting and
re-protecting the sheet. As a practical matter UserInterfaceOnly:=True was
not implemented perfectly by MS so there are some changes a developer still
has to unprotect a sheet to make.

--
Jim
| Using XL 2003
|
|
| Can someone explain the differences between:
|
| (A) ActiveSheet.Protect UserInterfaceOnly:=True
| vs.
| (B) ActiveSheet.Unprotect
|
| In most cases where I have protected sheets, I can use (A) with no
problems.
|
| When attempting to use a macro to first delete then re-establish data
| validation with different parameters (in a selected Range), I ran into a
type
| of " lost contact with X or Y error".
|
| If I manually unprotected the sheet then ran the macro all was OK.
|
| What I found was
|
| Test Sub1()
| ActiveSheet.Unprotect
|
| ..
| .. "Data validation via VBA code"
| ..
| ..
| ..
| ..
|
| ActiveSheet.Protect ' works
| End Sub
|
|
| Test Sub2()
| ActiveSheet.Protect UserInterfaceOnly:=True
| ..
| ..
| .. "Data validation via VBA code"
| ..
| ..
| .. ' did not always work as mostly received
| ' error message mentioned above
| End Sub
|
| Any thoughts as to why?
|
| Dennis

 

[Guest]

Great and logical answer! My thought was that the "UserInterfaceOnly:" door
was "mainly" or "somwtimes"
Now I know what to do.

Thanks, Dennis