Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
ActiveSheet.Protect UserinterfaceOnly:=true
I need to make changes to a multiple sheet workbook. Some of the
cells are protected. I know the above command in my Subject line will allow this on a sheet by sheet basis, but I need something else. Is there a comand line that will allow changes to be made to a worksheet no matter what sheet is currently activated? Thanks for the help. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
ActiveSheet.Protect UserinterfaceOnly:=true
Tim,
Not sure that I understand what you mean??? ActiveSheet.Protect UserinterfaceOnly:=true Will protect the "Active" sheet and allow changes made via VBA. Note that the protection is volatile meaning that if the workbook is opened with macros disabled, the sheet won't be protected. This means that you have to run this code each time the workbook is opened and macros have to be enabled. Now you could specify a particular sheet or loop through groups of sheets: Worksheets("Sheet1").Protect UserinterfaceOnly:=true Worksheets("Sheet2").Protect UserinterfaceOnly:=true You don't have to have the sheet activated to protect it in this manner. Is there a comand line that will allow changes to be made to a worksheet no matter what sheet is currently activated? If the sheets are protected using the above method, you can have any sheet active and change data on another sheet. There isn't any specific command line to do this. If Sheets1 & 2 are protected as above and sheet2 is the "Active" sheet, Worksheets("Sheet1").Range("A1").Value = "abc" will work fine. John Tim wrote: I need to make changes to a multiple sheet workbook. Some of the cells are protected. I know the above command in my Subject line will allow this on a sheet by sheet basis, but I need something else. Is there a comand line that will allow changes to be made to a worksheet no matter what sheet is currently activated? Thanks for the help. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
ActiveSheet.Protect UserinterfaceOnly:=true
How are you doing?
The last workbook you helped me with is in use and the user loves it. Thanks for the help. As for this new posting, you have also answered it. I need to loop through the sheets setting each to :=true. I am writing a macro that copies data from an old file into a new template that has been updated for ease of use and errors. Both the old and new files have approx. 32 sheets and I need to copy/paste bits and peaces from/to each. I am trying not to activate a sheet because, even though I have set the screen update to false, it still seem to update the screen. With the code example you gave, I can allow changes to be made without activating each sheet. Thanks. *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
ActiveSheet.Protect UserinterfaceOnly:=true
Tim,
I'm doing fine. The last workbook you helped me with is in use and the user loves it. Good to hear Thanks for the help. You're quite welcome I need to loop through the sheets setting each to :=true. The following should work: Sub ProtectAll() Dim ws As Worksheet Application.ScreenUpdating = False For Each ws In ThisWorkbook.Worksheets ws.Protect UserinterfaceOnly:=True Next ws Application.ScreenUpdating = True End Sub John Tim Russell wrote: How are you doing? The last workbook you helped me with is in use and the user loves it. Thanks for the help. As for this new posting, you have also answered it. I need to loop through the sheets setting each to :=true. I am writing a macro that copies data from an old file into a new template that has been updated for ease of use and errors. Both the old and new files have approx. 32 sheets and I need to copy/paste bits and peaces from/to each. I am trying not to activate a sheet because, even though I have set the screen update to false, it still seem to update the screen. With the code example you gave, I can allow changes to be made without activating each sheet. Thanks. *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
ActiveSheet.Protect UserinterfaceOnly:=true
You stated:
Note that the protection is volatile meaning that if the workbook is opened with macros disabled, the sheet won't be protected. You might want to clarify that the worksheet remains protected - the setting of the UserInterfaceOnly option is the only volatile part - it will be set to false when the workbook is close/opened. In your example you did not set a value for the other three arguments and they default to True in that case (and these settings are not volatile) Regards, Tom Ogilvy "John Wilson" wrote in message ... Tim, I'm doing fine. The last workbook you helped me with is in use and the user loves it. Good to hear Thanks for the help. You're quite welcome I need to loop through the sheets setting each to :=true. The following should work: Sub ProtectAll() Dim ws As Worksheet Application.ScreenUpdating = False For Each ws In ThisWorkbook.Worksheets ws.Protect UserinterfaceOnly:=True Next ws Application.ScreenUpdating = True End Sub John Tim Russell wrote: How are you doing? The last workbook you helped me with is in use and the user loves it. Thanks for the help. As for this new posting, you have also answered it. I need to loop through the sheets setting each to :=true. I am writing a macro that copies data from an old file into a new template that has been updated for ease of use and errors. Both the old and new files have approx. 32 sheets and I need to copy/paste bits and peaces from/to each. I am trying not to activate a sheet because, even though I have set the screen update to false, it still seem to update the screen. With the code example you gave, I can allow changes to be made without activating each sheet. Thanks. *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Userinterfaceonly | Excel Worksheet Functions | |||
UserInterfaceOnly & Password Protection | Excel Discussion (Misc queries) | |||
UserInterfaceOnly | Excel Discussion (Misc queries) | |||
Problem with using Protect when userinterfaceonly:=True | Excel Programming | |||
Protection UserInterfaceOnly reading Hidden Formulas | Excel Programming |