Automatically Protect/UnProtect All Worksheets

Tonight I want to automatically protect and unprotect all Microsoft Excel Worksheets in my Workbook with password, instead of doing it manually, I put these following Excel VBA macro code in my Workbook.

Public Sub ProtectAllSheets()

    Dim objSheet As Worksheet

    'Protecting all worksheets with password
    For Each objSheet In Worksheets
        If objSheet.ProtectContents = False Then objSheet.Protect "a@#&ladfl&^"
    Next objSheet

End Sub

Public Sub UnProtectAllSheets()

    Dim objSheet As Worksheet

    'UnProtecting all worksheets with password
    For Each objSheet In Worksheets
        If objSheet.ProtectContents = True Then objSheet.Unprotect "a@#&ladfl&^"
    Next objSheet

End Sub

We only need to call the procedure ProtectAllSheets to protect with password all Excel Worksheets in our Workbook, and calling UnProtectAllSheets will reversed the effect.

This entry was posted in Workbook and Worksheet and tagged , , , , , , , . Bookmark the permalink.

4 Responses to Automatically Protect/UnProtect All Worksheets

  1. Yoav says:

    Hi,

    Wouldn’t the password be plainly visible to anyone looking at the VB code? Doesn’t it defeat the purpose of having a password?

    I am trying to achieve something similar and came across your post in a search.

    Do you know whether it is possible to have the code pop-up a password box such that you will have to put in your password in order to un/protect the worksheets in a way that you will not have to write your password into the script?

    Cheers,

    Yoav

  2. Pingback: reverse mortgage pros and cons

  3. Pingback: reverse mortgage lender

  4. Pingback: deutsch connectors

Leave a Reply

Your email address will not be published. Required fields are marked *

*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>