Results 1 to 5 of 5
  1. #1
    4 Star Lounger
    Join Date
    Dec 2003
    Location
    Perth, Western Australia, Australia
    Posts
    487
    Thanks
    75
    Thanked 2 Times in 1 Post

    Password protect all tabs

    Loungers, I have a spreadsheet that has multiple tabs.

    I am after some code to password protect all tabs in the spreadsheet, except one colum "P" in a tab called WHS.

    Any assistance would be appreciated.

    Thanks in advance

  2. #2
    4 Star Lounger
    Join Date
    Dec 2003
    Location
    Perth, Western Australia, Australia
    Posts
    487
    Thanks
    75
    Thanked 2 Times in 1 Post
    Anyone?

  3. #3
    3 Star Lounger
    Join Date
    Apr 2012
    Posts
    240
    Thanks
    3
    Thanked 24 Times in 24 Posts
    I found this code a while ago that works great for protecting/unprotecting all sheets. Not sure how you would modify it to leave one sheet unprotected. Could you just unprotect the cells required?

    Code:
    Dim ws As Worksheet
    Sub ProtectAll()
        Dim S As Object
        Dim pWord1 As String, pWord2 As String
        pWord1 = InputBox("Please Enter the password")
        If pWord1 = "" Then Exit Sub
        pWord2 = InputBox("Please re-enter the password")
         
        If pWord2 = "" Then Exit Sub
         'make certain passwords are identical
        If InStr(1, pWord2, pWord1, 0) = 0 Or _
        InStr(1, pWord1, pWord2, 0) = 0 Then
            MsgBox "You entered different passwords. No action taken"
            Exit Sub
        End If
        For Each ws In Worksheets
            ws.Protect Password:=pWord1
        Next
        MsgBox "All sheets Protected."
        Exit Sub
         
    End Sub
     
    Sub UnProtectAll()
        Dim S As Object
        Dim pWord3 As String
        pWord3 = InputBox("Please Enter the password")
        If pWord3 = "" Then Exit Sub
        For Each ws In Worksheets
            On Error GoTo errorTrap1
            ws.Unprotect Password:=pWord3
        Next
        MsgBox "All sheets UnProtected."
        Exit Sub
         
    errorTrap1:
        MsgBox "Sheets could not be UnProtected - Password Incorrect"
        Exit Sub
         
         
    End Sub

  4. #4
    4 Star Lounger
    Join Date
    Dec 2003
    Location
    Perth, Western Australia, Australia
    Posts
    487
    Thanks
    75
    Thanked 2 Times in 1 Post
    Thanks BigMac - will give it a shot

  5. #5
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Verada,

    Another approach is to select Col P in the WHS worksheet, right-click Format Cells and turn off Locked on the Protection tab.
    Then use the code below:
    Code:
    Option Explicit
    
    Sub ProtectAllSheets()
    
       Dim shtCur As Worksheet
       
       For Each shtCur In Worksheets
           shtCur.Protect Password:="Test", DrawingObjects:=True, Contents:=True, Scenarios:=True
       Next shtCur
       
    End Sub
    
    Sub UnProtectAllSheets()
    
       Dim shtCur As Worksheet
       
       For Each shtCur In Worksheets
           shtCur.Unprotect Password:="Test"
       Next shtCur
    
    End Sub
    Note: Change the Password or delete the entire Password item as your requirements merit.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

Posting Permissions

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