Consulting

Results 1 to 5 of 5

Thread: Password protection with exceptions

  1. #1

    Password protection with exceptions

    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!

  2. #2
    Site Admin
    Jedi Master
    VBAX Guru Jacob Hilderbrand's Avatar
    Joined
    Jun 2004
    Location
    Roseville, CA
    Posts
    3,712
    Location
    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

  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,712
    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,940
    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
  •