REAL-TIME

VBA Projects

Full Access with Source Code
  • Designed and Developed by PNRao

  • Full Access with VBA Source Code

  • Well Commented Codes Lines

  • Creative and Professional Design

Effortlessly
Manage Your Projects

120+ Project Management Templates

Seamlessly manage your projects with our powerful & multi-purpose templates for project management.

120+ PM Templates Includes:
  • 50+ Excel Templates

  • 50+ PowerPoint Templates

  • 25+ Word Templates

Share Post

Description:

Protect and UnProtect Worksheets in Excel VBA is useful when we work with the templates or some worksheets which structure will not change but data may change. Or we may want to restrict the users to not to modify the structure of the worksheet.

Protect UnProtect Worksheet in Excel VBA – Solution:

Protect UnProtect Worksheet in Excel VBA
We can use Protect and UnProtect Methods of a Worksheet to Protect and UnProtect Worksheets in Excel using VBA.

Protect UnProtect Worksheet in Excel VBA – Examples:

The following example will show you how to protect and unprotect worksheets in Excel using VBA.

Example to Protect Worksheet
Sub sbProtectSheet()

    ActiveSheet.Protect "password", True, True

End Sub

Here the left side part is the sheet which you want to protect. the first parameter will be password: you an provide any password to protect worksheet.

Example to UnProtect Worksheet
Sub sbUnProtectSheet()

    ActiveSheet.Unprotect "password"

End Sub

Here the leftside part is the worksheet which you want to un-protect and the right side is the password to unprotect it which you have provided while protecting the worksheet.

Instructions:
  1. Open an excel workbook
  2. Press Alt+F11 to open VBA Editor
  3. Insert a Module for Insert Menu
  4. Copy the above code and Paste in the code window
  5. Save the file as macro enabled workbook
  6. Press F5 to execute it

Protect All Worksheets in Workbook

We can Protect All Worksheets in Workbook using VBA. If you want to restrict all worksheets from users not to modify the structure, we can protect all worksheets.

Protect All Worksheets in Workbook using VBA – Solution:

Protect All Worksheets in Workbook using VBA
We can use Protect method and iterate all the worksheets in a workbook.

Protect All Worksheets in Workbook using VBA – Exmaple:

Follwoing is the example code will show you how to Protect All Worksheets in Workbook using VBA.

Code
Sub sbProtectAllSheets()

    Dim pwd1 As String, pwd2 As String
    pwd1 = InputBox("Please Enter the password")
    If pwd1 = "" Then Exit Sub
    pwd2 = InputBox("Please re-enter the password")

    If pwd2 = "" Then Exit Sub

     'Check if both the passwords are identical
    If InStr(1, pwd2, pwd1, 0) = 0 Or _
    InStr(1, pwd1, pwd2, 0) = 0 Then
        MsgBox "You entered different passwords. No action taken"
        Exit Sub
    End If

    For Each ws In Worksheets
        ws.Protect Password:=pwd1
    Next

    MsgBox "All sheets Protected."

    Exit Sub
    
End Sub
Instructions:
  1. Open an excel workbook
  2. Press Alt+F11 to open VBA Editor
  3. Insert a Module for Insert Menu
  4. Copy the above code and Paste in the code window
  5. Save the file as macro enabled workbook
  6. Press F5 to execute it

UnProtect All Worksheets in Workbook using VBA

We can UnProtect All Worksheets in Workbook using VBA. If you want to delete the restrictions of all worksheets, we can unprotect all worksheets at a time.

UnProtect All Worksheets in Workbook using VBA- Solution:

UnProtect All Worksheets in Workbook using VBA We can use Unprotect method and iterate all the worksheets in a workbook.

UnProtect All Worksheets in Workbook using VBA – Example:

Following is the example code will show you how to UnProtect All Worksheets in Workbook using VBA.

Code
Sub sbUnProtectAll()
  
    On Error GoTo ErrorOccured
   
    Dim pwd1 As String
    pwd1 = InputBox("Please Enter the password")
    If pwd1 = "" Then Exit Sub
    For Each ws In Worksheets        
        ws.Unprotect Password:=pwd1
    Next
    MsgBox "All sheets UnProtected."

    Exit Sub
     
ErrorOccured:
    MsgBox "Sheets could not be UnProtected - Password Incorrect"
    Exit Sub
    
End Sub
Instructions:
  1. Open an excel workbook
  2. Press Alt+F11 to open VBA Editor
  3. Insert a Module for Insert Menu
  4. Copy the above code and Paste in the code window
  5. Save the file as macro enabled workbook
  6. Press F5 to execute it

Show Worksheet Protect dialog in Excel VBA

You can Show Worksheet Protect dialog to give the user to enter password to protect worksheets. So that user can have the option to enter required password.

Show Worksheet Protect dialog – Solution:

Show Worksheet Protect dialog in Excel VBA
We can use Application.Dialogs(xlDialogProtectDocument).Show method to Show Worksheet Protect dialog.

Show Worksheet Protect dialog – Example:

Following is the example to Show you how to do this.

Code

Sub sbShowProtectDialogBox()
'This code will do the required task...
Application.Dialogs(xlDialogProtectDocument).Show

End Sub
Instructions:

Follow the instructions below to execute the code.

  1. Open an excel workbook
  2. Press Alt+F11 to open VBA Editor
  3. Insert a Module for Insert Menu
  4. Copy the above code and Paste in the code window
  5. Save the file as macro enabled workbook
  6. Press F5 to execute it
Effortlessly Manage Your Projects and Resources
120+ Professional Project Management Templates!

A Powerful & Multi-purpose Templates for project management. Now seamlessly manage your projects, tasks, meetings, presentations, teams, customers, stakeholders and time. This page describes all the amazing new features and options that come with our premium templates.

Save Up to 85% LIMITED TIME OFFER
Project Management Templates

All-in-One Pack
120+ Project Management Templates

Essential Pack
50+ PM Templates

Excel Pack
50+ Excel PM Templates

PowerPoint Pack
50+ Excel PM Templates

MS Word Pack
25+ Word PM Templates

Ultimate Project
Management Template
Ultimate Resource
Management Template
Project Portfolio
Management Templates
Categories: Excel VBATags: Last Updated: June 17, 2022

7 Comments

  1. Davdi Harding February 8, 2015 at 4:47 PM

    Just bear in mind that under Excel 2013 this process takes over 4000 times longer to do than it did under previous version, so it’s probably best not to do ‘All sheets’ if it’s already after lunch, because it won’t finish before you want to go home, and in the meantime Excel 2013 will be completely unusable, and you can’t ctrl- break to stop it from running.
    Please also note that this is NOT a bug, it is by design and will not be changed

  2. Avishek February 23, 2015 at 5:26 PM

    Great. It’s really helpful.

  3. Chad April 3, 2015 at 12:57 AM

    This is exactly true. Due the the performance hit this has caused, Microsoft needs to let the user assign levels of encryption. Reasons for protecting a sheet are not always to protect sensitive data. Many times the only reason for protection a sheet is to prevent the user from making inadvertent changes. There are many instances where you cannot get away from unprotecting and protecting sheet such as importing data from several protected files, or using a query which will not run protected even in interface only. Excel 2013 is a hug disappointment performance wise. We can only hope serious changes are made with the next version.

  4. Iain October 13, 2015 at 5:51 AM

    Hello

    Thanks for all this code, it’s so helpful when everyone shares.

    I have a question relating to this topic. Can one protect a sheet, but allow for the following
    1. Select Locked Cells
    2. Select Unlocked Cells
    3. Sort
    4. filter

    Thanks in advance

  5. Frank November 2, 2016 at 1:26 PM

    I’m also interested in knowing if this is possible

  6. amit September 28, 2018 at 5:32 AM

    I used this formula its working but need password in form or mask

    Sub sbUnProtectAll()

    On Error GoTo ErrorOccured

    Dim pwd1 As String
    pwd1 = InputBox(“Please Enter the password”)
    If pwd1 = ” Then Exit Sub
    For Each ws In Worksheets
    ws.Unprotect Password:=pwd1
    Next
    MsgBox “All sheets UnProtected.”

    Exit Sub

    ErrorOccured:
    MsgBox “Sheets could not be UnProtected – Password Incorrect”
    Exit Sub

    End Sub

  7. amit September 28, 2018 at 5:33 AM

    in form of mask not in word “password” should be shown as “xxxxxxxxx”

Leave A Comment