+ Reply to Thread
Results 1 to 14 of 14

protecting sheets

  1. #1
    Registered User
    Join Date
    08-11-2009
    Location
    New York, New York
    MS-Off Ver
    Excel 2004
    Posts
    17

    protecting sheets

    is it possible to protect many sheets at once?

    i've formatted cells in multiple sheets as "locked": i gather that i need to select "protect sheet" for the "locked" formatting to be in effect.

    when i select multiple sheets, the "protect sheet" option is greyed.
    I have many sheets, would be very handy to be able to protect/unprotect in one step...
    Last edited by romperstomper; 06-17-2011 at 09:05 AM.

  2. #2
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,578

    Re: protecting sheets

    This should work
    Please Login or Register  to view this content.
    Is that what you need?
    ChemistB
    My 2?

    substitute commas with semi-colons if your region settings requires
    Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
    If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)

    Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble

  3. #3
    Registered User
    Join Date
    08-11-2009
    Location
    New York, New York
    MS-Off Ver
    Excel 2004
    Posts
    17

    Re: protecting sheets

    i'm not familiar with where that kind of code gets entered/compiled/activated.
    pls advise, thanks!

  4. #4
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,578

    Re: protecting sheets

    From your spreadsheet, ALT + F11 gets you to the VB Editor.
    Insert>Module
    Paste code

    Then you can close the VB Editor (no need to save, it's linked to the workbook)
    From the workbook, Tools>macros>macros and run that macro.
    Questions?

  5. #5
    Registered User
    Join Date
    08-11-2009
    Location
    New York, New York
    MS-Off Ver
    Excel 2004
    Posts
    17

    Re: protecting sheets

    i'm on a mac, running excel2004, so the alt+F11 didn't get me there, but i found my way to it, and guessed correctly at an "Unprotect" counterpart macro. Both work great, thanks for the help!

  6. #6
    Registered User
    Join Date
    03-29-2010
    Location
    Kyle, Texas
    MS-Off Ver
    Excel 2010
    Posts
    33

    Re: protecting sheets

    This was SO helpful for me but I have one questions.

    In order to UNPROTECT these sheets do you create a macro with the word Unprotect in place of the Protect?

    Sub ProtectSheets()
    Dim s As Worksheet
    Dim Pwd As String

    Pwd = InputBox("Enter Password")
    For Each s In ActiveWorkbook.Worksheets
    s.Protect Password:=Pwd
    Next
    End Sub
    I tried this on a practice workbook and it worked, but I want to be sure this is correct before I add to my workbook.

    Can someone please confirm.
    David

  7. #7
    Forum Expert dominicb's Avatar
    Join Date
    01-25-2005
    Location
    Lancashire, England
    MS-Off Ver
    MS Office 2000, 2003, 2007 & 2016 365
    Posts
    4,867

    Smile Re: protecting sheets

    Good afternoon Big_Tater
    Quote Originally Posted by Big_Tater View Post
    In order to UNPROTECT these sheets do you create a macro with the word Unprotect in place of the Protect?
    Absolutely.
    And well done on taking the initiative and trying it. Welcome to the VBA rocky road to ruin ...

    DominicB
    Please familiarise yourself with the rules before posting. You can find them here.

  8. #8
    Registered User
    Join Date
    03-29-2010
    Location
    Kyle, Texas
    MS-Off Ver
    Excel 2010
    Posts
    33

    Re: protecting sheets

    Quote Originally Posted by dominicb View Post
    Good afternoon Big_Tater
    Absolutely.
    And well done on taking the initiative and trying it. Welcome to the VBA rocky road to ruin ...
    DominicB
    LMAO! Thanks Dominic. Always nice to get confirmation before breaking something you've worked on for a week. :-)

    I also looked at your Excel 2007 Add-In (Ultimate) Beta and am extremely interested in that as well. Just need to get my IT departments approval to let me install it.

    Thanks again for the quick reply.

  9. #9
    Registered User
    Join Date
    03-29-2010
    Location
    Kyle, Texas
    MS-Off Ver
    Excel 2010
    Posts
    33

    Re: protecting sheets

    Sub ProtectSheets()
    Dim s As Worksheet
    Dim Pwd As String

    Pwd = InputBox("Enter Password")
    For Each s In ActiveWorkbook.Worksheets
    s.Protect Password:="Money"
    Next
    End Sub
    Well so I used the above to lock down our accounting worksheet so all of the formulas I worked on don't get erased accidentally. The password Input box opens perfectly BUT you don't have to actually enter a password to enable or disable the protection. (The above code is what I have in my Workbook.)

    Thanks.
    David

  10. #10
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,578

    Re: protecting sheets

    You don't want to hardcode "Money" into your code. you should be using Pwd in that spot like so
    Please Login or Register  to view this content.
    Does that work for you?

  11. #11
    Registered User
    Join Date
    03-29-2010
    Location
    Kyle, Texas
    MS-Off Ver
    Excel 2010
    Posts
    33

    Re: protecting sheets

    Perfect! Thanks ChemistB that is exactly what I needed. I'm not real familiar with Macros or VBA so didnt know about the hard coding. (Just didn't want the accounting team erasing what I spend weeks building.)

    This forum is SOOO helpful!

  12. #12
    Registered User
    Join Date
    09-08-2009
    Location
    Hawaii
    MS-Off Ver
    Excel 2013
    Posts
    68

    Re: protecting sheets

    Is there a way to provide a second display to confirm password? Just in case I fat finger the first password.

  13. #13
    Registered User
    Join Date
    03-29-2010
    Location
    Kyle, Texas
    MS-Off Ver
    Excel 2010
    Posts
    33

    Re: protecting sheets

    So I've hit the "danger zone" on a super critical spreadsheet.

    I gave the password to one of the folks in my user group and they have somehow changed the password to the spreadsheet and I can no longer unlock the spreadsheet due to a changed password.

    Is there a way to find what the password was changed too or to change it back to what I set it too.

    Thanks.

  14. #14
    Registered User
    Join Date
    06-07-2011
    Location
    Ontario, Canada
    MS-Off Ver
    Excel 2003
    Posts
    11

    Re: protecting sheets

    Fantastic...works great...thanks a million

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Tags for this Thread

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1