Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Difference between Activesheet.unprotect
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 |
#2
|
|||
|
|||
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 "Dennis" wrote in message ... | 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 |
#3
|
|||
|
|||
Great and logical answer! My thought was that the "UserInterfaceOnly:" door
was "mainly" or "somwtimes" Now I know what to do. Thanks, Dennis "Dennis" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formula to find the working days difference between to dates? | Excel Discussion (Misc queries) | |||
Date difference | Excel Worksheet Functions | |||
charting a difference of 2 columns' w/o adding a difference column | Charts and Charting in Excel | |||
Cell formats, and time difference | Excel Discussion (Misc queries) | |||
How to get difference in hours | Excel Worksheet Functions |