Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Page 2 of 3 FirstFirst 1 2 3 LastLast
Results 11 to 20 of 22

Thread: Macros to Protect/Unprotect Worksheets and Workbooks

  1. #11
    Join Date
    26th January 2003
    Location
    Derbyshire,UK
    Posts
    18,340

    Re: Macros to Protect/Unprotect Worksheets and Workbooks

    Windy you shouldn't ask questions here. Use the VBA Forum & put a link to this in your explanation. You can however look at Barry's example of excluding a sheet from a loop
    Hope that Helps

    Roy

    For free Excel tools & articles visit my web site

    If I have helped you and you feel like putting your hand in your pocket please make a donation to Children in Need

    RoyUK's Web Site

    royUK's Database Form

    Where to paste code from the Forum

    About me.

  2. #12
    Join Date
    1st July 2005
    Posts
    9

    Re: Macros to Protect/Unprotect Worksheets and Workbooks

    Hi Windy58,

    Try this:Instead of "Sheet 2", enter the name of your worksheet.

    VB:
    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 
            Sheets("Sheet2").Unprotect 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 
    
    

    Excel Video Tutorials / Excel Dashboards Reports


  3. #13
    Join Date
    17th November 2005
    Location
    Kent
    Posts
    640

    Re: Macros to Protect/Unprotect Worksheets and Workbooks

    Thanks Luttrrt. Didn't realise it would be that simple......

    Excel Video Tutorials / Excel Dashboards Reports


  4. #14
    Join Date
    26th January 2003
    Location
    Derbyshire,UK
    Posts
    18,340

    Re: Macros to Protect/Unprotect Worksheets and Workbooks

    That amendment will try to unprotect Sheet2 for each loop.
    Hope that Helps

    Roy

    For free Excel tools & articles visit my web site

    If I have helped you and you feel like putting your hand in your pocket please make a donation to Children in Need

    RoyUK's Web Site

    royUK's Database Form

    Where to paste code from the Forum

    About me.

  5. #15
    Join Date
    20th April 2012
    Posts
    1

    Re: Macros to Protect/Unprotect Worksheets and Workbooks

    Quote Originally Posted by Windy58 View Post
    This is just what I need, but I want one sheet to remain unprotected how would I do that?

    -->This to Unprotect all sheets in your Workbook:

    VB:
    Dim myPWD As String 
    Dim wks As worksheet 
    myPWD = "hi" 
    For Each wks In activeworkbook.worksheets 
        wks.unprotect password:=mypwd 
    Next wks 
    
    
    -->At the end of ur macro script write the code for PROTECTING ALL WORKSHEETS

    VB:
    For Each wks In activeworkbook.worksheets 
        wks.Protect password:=mypwd 
    Next wks 
    
    
    -->To UnProtect a Particular sheet Type the below mentioned code after the above code: (where "My Work" is the worksheet name I have in my Excel
    workbook which I want to Unprotect)

    VB:
    Sheets("My Work").Unprotect Password:=myPWD 
    
    
    Last edited by Dave Hawley; April 20th, 2012 at 19:21. Reason: Code tags added PM sent

    Excel Video Tutorials / Excel Dashboards Reports


  6. #16
    Join Date
    18th November 2004
    Location
    God's Own Country
    Posts
    4,355

    Re: Macros to Protect/Unprotect Worksheets and Workbooks

    Hi Buzzy,

    Welcome to board !!

    Please edit your post and add code tags. [code] your code here [/ code] (without space)

  7. #17
    Join Date
    26th January 2003
    Location
    Derbyshire,UK
    Posts
    18,340

    Re: Macros to Protect/Unprotect Worksheets and Workbooks

    Shouldn't ask questions in this Forum

    Excel Video Tutorials / Excel Dashboards Reports


  8. #18
    Join Date
    18th November 2004
    Location
    God's Own Country
    Posts
    4,355

    Re: Macros to Protect/Unprotect Worksheets and Workbooks

    Hey Roy,

    Welcome back !!

  9. #19
    Join Date
    26th January 2003
    Location
    Derbyshire,UK
    Posts
    18,340

    Re: Macros to Protect/Unprotect Worksheets and Workbooks

    Hi Kris

    Thanks for the birthday greeting yesterday

    Excel Video Tutorials / Excel Dashboards Reports


  10. #20
    Join Date
    10th September 2003
    Location
    Orlando, FL
    Posts
    129

    Re: Macros to Protect/Unprotect Worksheets and Workbooks

    Luttrrt----

    Just wanted to say thanks for the VBA macros. I am doing project work at my new job, so I am giving the existing spreadsheets a complete facelift and then automating almost everything. Before (I arrived), formulas were constantly getting messed up due to an innocent error by the end user. You macros allow me an easy way to protect them, and then unprotect them when I need to make adjustments. Thanks again, these scripts are awesome!
    Sherry
    Orlando, FL

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Possible Answers

  1. Protect / Unprotect Worksheets
    By Aaron Stallmann in forum EXCEL HELP
    Replies: 6
    Last Post: March 8th, 2006, 07:37
  2. PROTECT & UNPROTECT via VBA
    By Michael Avidan in forum EXCEL HELP
    Replies: 2
    Last Post: February 19th, 2006, 02:45
  3. Protect and Unprotect All Worksheets
    By Dave Hawley in forum Excel FAQ
    Replies: 0
    Last Post: June 30th, 2005, 13:32
  4. [Solved] Protect and Unprotect
    By Chas49 in forum EXCEL HELP
    Replies: 3
    Last Post: August 21st, 2003, 04:22
  5. protect / unprotect
    By sekhar in forum EXCEL HELP
    Replies: 3
    Last Post: May 1st, 2003, 22:22

Bookmarks

Posting Permissions

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