Macros to Protect/Unprotect Worksheets and Workbooks

  • Hi,


    I wrote this code to make my life easier.Might come in handy for somebody else.The first macro protects all sheets in a Spreadsheet and the second unprotects.
    The the next Macro Protects the enitre workbook and likewise the next one Unprotects.
    All using passwords.





    Regards,
    Rowland

  • Re: Macros to Protect/Unprotect Worksheets and Workbooks


    Thanks, such a simple solution, awesome thanks. Just another question, how do I change the password? What I've done is the following:
    1) changed 'ws.Protect Password:=pWord1' only, without success;
    2) changed all the 'pWord1' (out of the macro) into my 'new password'.


    What did I do wrong?

  • Re: Macros to Protect/Unprotect Worksheets and Workbooks


    ummm...


    pWord1 is a string variable set from the value you type into the first InputBox (as long as you type it again correctly in the second inputbox).


    I can't see the need to change the variable name pWord1 .... just type your password into the inputbox(es)

  • Re: Macros to Protect/Unprotect Worksheets and Workbooks


    This is a great thing. I have a workbook with 18 or 19 sheets and I hate having to protect each and every one whenever I ned to make changes. Is it limited to 3 sheets? When I run the protect all sheets macro it ask for the password and then to confrim but when I go to unprotect it says incorrect password.


    The password I entered is 1. It's kinda hard to mess up 1. :) That was my trial run with the password to make sure it worked.


    Thanks,
    Gary

  • Re: Macros to Protect/Unprotect Worksheets and Workbooks


    WillR,


    Thanks, I like the way the one Luttrrt wrotes ask for the password but I got one to work from this thread http://www.ozgrid.com/forum/showthread.php?t=36862 it doesn't ask for the password the way the one Luttrrt wriote but it works.



    Thanks again,
    Gary

  • Re: Macros to Protect/Unprotect Worksheets and Workbooks


    Quote from WillR

    Any chance you could attach an example workbook where it doesn't work ? The procedures outlined above should be OK


    Thanks this one is solved. It did work. I had a sheet with a different password. Once agian thanks it worked.


    Thanks,
    Gary

  • 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

  • Re: Macros to Protect/Unprotect Worksheets and Workbooks


    Hi Windy58,


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


  • Re: Macros to Protect/Unprotect Worksheets and Workbooks


    Quote from Windy58;254430

    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:


    Code
    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


    Code
    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)


    Code
    Sheets("My Work").Unprotect Password:=myPWD
  • 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!

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!