ActiveSheet.Protect UserinterfaceOnly:=true

[Tim]

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.

 

[John Wilson]

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

 

[John Wilson]

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

 

[Tom Ogilvy]

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