This article will tel you how to unprotect an excel spread sheet without having the. Which can be used to unprotect the other sheets in the same workbook. How to unprotect Excel Workbook and Worksheet with or without password. To protect the data from unauthorized reading or editing by others, we set protection password on the Excel workbook, workbook structure or worksheet. However, when we forget the protected password, we can't read or edit the data on the Excel file too.
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.
50+ Project Management Templates Pack
Excel PowerPoint Word
Excel PowerPoint Word
Advanced Project Plan & Portfolio Template
Excel Template
Excel Template
Ultimate Project Management Template
Excel Template
Excel Template
20+ PowerPoint Project Management Pack
PowerPoint Templates
PowerPoint Templates
Protect UnProtect Worksheet in Excel VBA – Solution:
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
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
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:
- Open an excel workbook
- Press Alt+F11 to open VBA Editor
- Insert a Module for Insert Menu
- Copy the above code and Paste in the code window
- Save the file as macro enabled workbook
- 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:
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
Instructions:
- Open an excel workbook
- Press Alt+F11 to open VBA Editor
- Insert a Module for Insert Menu
- Copy the above code and Paste in the code window
- Save the file as macro enabled workbook
- 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:
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
Instructions:
- Open an excel workbook
- Press Alt+F11 to open VBA Editor
- Insert a Module for Insert Menu
- Copy the above code and Paste in the code window
- Save the file as macro enabled workbook
- 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:
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
Instructions:
Follow the instructions below to execute the code.
- Open an excel workbook
- Press Alt+F11 to open VBA Editor
- Insert a Module for Insert Menu
- Copy the above code and Paste in the code window
- Save the file as macro enabled workbook
- Press F5 to execute it
Related Resource
Excel VBA Reference | Project Management Reference |
---|---|
VBA Reference: Excel Reference: |
Excel VBA Reference | Project Management Reference |
---|---|
VBA Reference: Excel Reference: |