Protect and Unprotect a Sheet in Excel

It’s common for any Excel user to password-protect an Excel file, especially if your file contains complex formulae where cells are interlinked. You’ll want to avoid accidental edits that can break any formulae and distort your data completely. Although password-protecting is the best way to keep your data safe, managing to remove the password from Excel or read-only status can become a tedious task if you have forgotten the password.

Thankfully, Excel includes the Visual Basic for Applications (VBA) solution, which allows you to complete this process more quickly and efficiently. In this article, you’ll learn how to write VBA codes and how to use them to protect and unprotect an Excel sheet, with and without a password. As an alternative option, Layer can be used on top of Excel spreadsheets to achieve advanced online collaboration.

How to enable the Developer tab in Excel?

Before learning how you can protect an Excel sheet without a password using VBA, make sure you have the “Developer” tab enabled for easier access.

1. 1. Open the Excel sheet you’d like to protect without a password. Go to File > Options.

1. 2. Select “Customize Ribbon” from the left-side menu. The “Developer” option should appear as selected, as shown below. Click “OK” to finish.

1. 3. You should now see an additional tab in your Excel named “Developer”. Select “Visual Basic” from the far left, to launch VBA.

1. 4. The VBA Editor should show up automatically, as illustrated below.

Protect a Sheet without a Password with VBA

Protecting a sheet without a password is a simple way of restricting others from making accidental changes to your data while maintaining their access. Although this can be done manually if you only have one sheet to protect, it will become a tedious process if you have more than one sheet that needs to be protected.

1. 1. Launch the VBA solution as shown and enter the following code in the first window.

Sub ProjectList23() 'Protect a worksheet Sheets("Main").Protect End Sub.

Here, "ProjectList23" and "Main" are the variable parameters that you’ll need to adapt to the name of your workbook and worksheet.

1. 2. Press “Enter” to execute the code.

1. 3. Excel may prompt you with the following “Can’t AutoSave” warning message. Select “Save As...” to change the format of the current file.

1. 4. If you try to “Save” directly, it will once again provide you with features that are incompatible with this type of format. Click “No”.

1. 5. Change the format by selecting “Excel Macro-Enabled Workbook (*.xlsm)” from the drop-down menu next to “Save”. Click “Save” to finish.

1. 6. You can now check if your sheet is protected by heading to the “Review” tab. It should now say “Unprotect Sheet”.

Now, you’ll learn how to do the reverse process using VBA.

Unprotect a Sheet without a Password with VBA

1. 1. Launch VBA and type in the following code, once again adapted to the name of your file and sheet.

Sub ProjectList23() 'Unprotect a worksheet Sheets("Main").Unprotect End Sub

1. 2. Check to see if your sheet is now unprotected by heading to the “Review” tab. It should now say “Protect Sheet”.

So far, you’ve seen how to protect and unprotect a sheet without a password. If your data requires an additional level of protection, you can add a password using a similar VBA solution.

Protect a Sheet with a Password with VBA

Similar to what you did previously, this method will require using a straightforward code adapted to the name of your file, sheet, and then your choice of password.

1. 1. Open the Excel workbook and activate the sheet you want to password protect with VBA. Type in the following code:

Sub ProjectList23() 'Protect worksheet with a password Sheets("Main").Protect Password:="H4iZ1LL" End Sub

1. 2. To check that the code ran successfully, try clicking on any cell in the password-protected sheet. Excel should prompt you with the following message.

Instead of entering the password, let’s see an easier way to unprotect the sheet using VBA.

Unprotect a Sheet with a Password with VBA

1. 1. Open the Excel workbook and the sheet you want to unprotect. Type in the following code to remove the password.

Sub ProjectList23() 'Unprotect worksheet with a password Sheets("Main").Unprotect Password:="H4iZ1LL" End Sub

1. 2. Check that the code ran successfully by heading to the Excel sheet and double-clicking on any cell. It should now allow you without prompting any warning or error message.

Although the codes shown are straightforward enough, you can also use the parameter activesheet.unprotect password and activesheet.protect password instead of the sheet name. You will simply have to make sure that the sheet you’d like to protect and unprotect is active.

Moreover, the code can adjust to the type and level of protection. For example, the VBA protect sheet code allows you to select locked cells to unprotect. This is very useful as they help speed up daily tasks, which can then be recorded as an Excel macro that you can run at any time instead of unlocking cells manually.

Conclusion

No matter the size or complexity of your dataset, if you are an Excel user, you’ll find that learning about VBA is definitely a plus. With basic and straightforward coding, you can save time completing daily and necessary tasks that usually take up most of your time. For example, protecting specific data to avoid losing data or breaking complex formulae.

You should now know how to enable quick access to VBA through the “Developer” tab and how to use four different VBA codes to protect and unprotect sheets in Excel, with and without a password.

If you prefer working on Google Sheets, then you may find How to Password-Protect a Google Sheet more suitable.