Excel VBA Macro

Automatically Protect/UnProtect All Worksheets

Posted on the September 26th, 2008 under Workbook and Worksheet by Poer @ Excel VBA/Macro

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.

Related Entries

• Schedules to Refresh Data Automatically using VBA Macro
• Check If Excel Workbook is Already Open or Not
• Automatically Run an Excel Macro
• Create New Excel Worksheet With VBA
• Excel VBA Add Set of Worksheets Automatically

External Resources

• Microsoft Excel 2003/2007 Video Tutorials

  Step-by-step video guide to mastering Charts, PivotTable, Data Analysis and Macro programming in Microsoft Excel in 5 hours.

• 101 Secrets of Microsoft Excel

  Discover 101 of Excels little-known secrets that have been hiding right under your nose.

Tagged with: activesheet unprotect, excel automatically protect unprotect, excel password, microsoft excel password, protect cell, protect vba, unprotect excel worksheet