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.

Updated on: 11-Jan-2023

181 Views

Kickstart Your Career

Get certified by completing the course

Get Started
Advertisements