Data Structure
Networking
RDBMS
Operating System
Java
MS Excel
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
Physics
Chemistry
Biology
Mathematics
English
Economics
Psychology
Social Studies
Fashion Studies
Legal Studies
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
How to Auto-Protect a Specific Worksheet in Excel?
In general, protection is used in Excel to prevent users from editing it. However, we tend to forget to protect the sheet, but we can automate it by using the VBA application. It will allow you to save the sheet with and without protection. Read this tutorial to learn how you can autoprotect a specific worksheet in Excel.
Auto-Protect a Specific Worksheet
Here we will insert a VBA code into the whole workbook, and then every sheet will be protected. Let us see a simple process to know how we can auto-protect a specific worksheet in Excel using the vba code, as it can’t be completed directly. Even if we unprotect the sheet to edit it, the sheet will be protected automatically when we save it.
Step 1
Consider creating a new Excel sheet, then right-clicking on the sheet name and choosing "View code" to open the VBA application, then double-clicking on this workbook, and typing the programme into the sheet as shown in the image below.
Example
Private Sub Workbook_BeforeClose(Cancel As Boolean) Dim ws As Worksheet ‘Updated by Nirmal For Each ws In ThisWorkbook.Worksheets ws.Protect "password" Next ws ThisWorkbook.Protect "password", True ThisWorkbook.Save End Sub
In the code, Book1 is the name of the sheet, and Nirmal is the password to unlock the workbook, but we can change them based on our requirements.
Step 2
Now save the workbook with macros enabled, and our workbook will be protected automatically. Close the workbook, and when you open the workbook next time, click on the enable code button, and if you try to edit the sheet, we can see that the sheet will be protected as shown in the below image.
Conclusion
In this tutorial, we used a simple example to demonstrate how we can auto-protect a specific worksheet in Excel.
183 Views
