Consulting

Results 1 to 5 of 5

Thread: Solved: Password protection with exceptions

  1. #1

    Solved: Password protection with exceptions

    DRJ, if you're listening:

    I have been using the following code:

    [VBA]
    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
    [/VBA]

    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!

  2. #2
    Site Admin
    Jedi Master
    VBAX Guru Jacob Hilderbrand's Avatar
    Joined
    Jun 2004
    Location
    Roseville, CA
    Posts
    3,710
    Location
    Try this.
    [vba]
    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
    [/vba]

  3. #3
    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)

  4. #4
    Site Admin
    Jedi Master
    VBAX Guru Jacob Hilderbrand's Avatar
    Joined
    Jun 2004
    Location
    Roseville, CA
    Posts
    3,710
    Location
    You're Welcome

    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.

  5. #5
    Site Admin
    Urban Myth
    VBAX Guru
    Joined
    May 2004
    Location
    Oregon, United States
    Posts
    4,897
    Location
    Hi,

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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •