Password protection with exceptions

[K. Georgiadis]

DRJ, if you're listening:

I have been using the following code:


Option Explicit

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
Next wsSheet
End Sub


It has been working quite well for me (the sole exception being a conflict with the Pivot Table's auto-refresh feature).

What statements can I add to the above to also allow formatting of cells and rows?

Thanks!

[Jacob Hilderbrand]

Try this.


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

[K. Georgiadis]

Thanks DRJ!

By the way, where do I find a list of VBA statements such as AllowFormattingRows. I can't find them either in the VBA Object Browser or in my VBA book (John Walkenbach's Excel Power Programming with VBA)

[Jacob Hilderbrand]

You're Welcome :beerchug:

You can see the Arguments when you are writing the code. For example type Sheet1.Protect and you should see a list of all the available Arguments.

[Zack Barresse]

Hi,

Also check out the Object Model (F2 while in the VBE). The most comprehensive list you'll ever find. :)