Worksheet Protection with exceptions

[K. Georgiadis]

Here I go again, wishing to expand the list of exceptions in the following code which I got here (from DRJ):

Public Sub ProtectAll()
Const PWORD As String = "MySecretWord"
Dim wsSheet As Worksheet
For Each wsSheet In Worksheets
wsSheet.Protect Password:=PWORD, DrawingObjects:=False, _
Contents:=True, Scenarios:=True, AllowFiltering:=True, _
AllowUsingPivotTables:=True, UserInterfaceOnly:=True, _
AllowFormattingCells:=True, AllowFormattingRows:=True
Next wsSheet
End Sub

I want to also allow inserting rows. I attempted AllowInsertingRows:=True and InsertingRows:=False, but neither works. Could you a lend a hand with this?

Is there a more convenient place to find these VBA expressions than in Microsoft's Online Help? I find it extremely difficult to find anything there.

Thanks for your help

[Jacob Hilderbrand]

AllowInsertingRows:=True

That is the correct line. I just use the Macro Recorder and make the changes manually then see what the code is.

[K. Georgiadis]

Thank you, I think I may have forgotten to separate the last two statements with a comma.

Now, I'm getting a phenomenon which will be a little disconcerting to a first time user of my workbook. Having protected the workbook with the above code, I also have the following code in "This Workbook" that is supposed to unprotect the workbook, refresh the Pivot Table and then re-protect:

Private Sub Workbook_Open()
Dim ws As Worksheet
  Dim pt As PivotTable
On Error Resume Next
For Each ws In ActiveWorkbook.Worksheets
  ws.Unprotect Password:="mySecretWord"
  For Each pt In ws.PivotTables
  pt.RefreshTable
  Next
  ws.Protect DrawingObjects:=False, _
  Contents:=True, Scenarios:=True, _
  AllowUsingPivotTables:=True, UserInterfaceOnly:=True, _
  AllowFormattingCells:=True, AllowFormattingRows:=True
  Password:="MySecretWord"
  Next
UserForm1.Show
  Sheets(?Instructions?).Select
  Range(?A1?).Select
 End Sub

When the workbook opens, there is a dizzying flurry of activity, as though Excel is rapidly cycling through all the worksheets. Not damaging but, as I said, a little disconcerting. Is it the combination of these two codes that cause this to happen and how might I prevent it?

Thank you

[Jacob Hilderbrand]

Put this at the start of your macro.

Application.ScreenUpdating = False

[K. Georgiadis]

Terrific!

[Jacob Hilderbrand]

Glad to help.

Take Care

[K. Georgiadis]

I am on the East coast of the US and noticed that your message is stamped 1:55 AM, 5 hours ahead of me. Is this website floating in the middle of the Atlantic Ocean?

[Jacob Hilderbrand]

You have your time zone set to GMT. Click on User CP then Edit Options. Set your time zone to Eastern and all the times will be correct for you.