How to Automatically Protect All Worksheets When Closing an Excel Workbook?

In general, protection is used in Excel to prevent users from editing it. However, we will always forget to protect the sheet, but we can automate it by using the VBA application. This tutorial will help you understand how we can automatically protect all worksheets when closing the Excel workbook. Protecting the sheet is nothing; the sheet will be in read-only mode, and we will not be able to edit the contents of the sheet. In this tutorial, we will be saving the whole workbook using the save workbook command.

Automatically Protect All Worksheets When Closing an Excel Workbook

Here we will insert a VBA code for the whole workbook along with a password. Let us see a simple process to know how we can automatically protect all worksheets when closing the Excel workbook. We will be using the VBA application to complete our process.

Step 1

Let us consider an Excel workbook that contains multiple worksheets, and right-click on the sheet name and select View Code to open the vba application. Then double-click on this workbook and copy the below-mentioned programme into the textbox as shown in the below image.

Example

Private Sub Workbook_BeforeClose(Cancel As Boolean)
'Update by Nirmal
   Dim xSheet As Worksheet
   Dim xPsw As String
   xPsw = "1234"
   For Each xSheet In Worksheets
      xSheet.Protect xPsw
   Next
End Sub

In the above code, 1234 is the password to unlock the workbook.

Step 2

Now save the sheet as a macro-enabled template, close the VBA application using the command "Alt + Q", and then close the workbook. When we try to edit the worksheet from now on, an error message will appear, as shown in the image below.

We can unlock the sheet by clicking on "unprotect" and entering the password 1234.

Conclusion

In this tutorial, we used a simple example to demonstrate how we can automatically protect all worksheets when closing an Excel workbook.

Pradeep Kumar

e

Updated on: 11-Jan-2023

2K+ Views

Kickstart Your Career

Get certified by completing the course

Get Started