Don’t forget to share this post

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.

Layer google sheets add on offer
Get Started With Layer Today!

Share parts of your Google Sheets, monitor, review and approve changes, and sync data from different sources – all within seconds.

GET STARTED FOR FREE

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.
Protect and Unprotect a Sheet in Excel File Options
Protect and Unprotect a Sheet in Excel - 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.
Protect and Unprotect a Sheet in Excel Customize Ribbon
Protect and Unprotect a Sheet in Excel - Customize Ribbon
  1. 3. You should now see an additional tab in your Excel named “Developer”. Select “Visual Basic” from the far left, to launch VBA.
Protect and Unprotect a Sheet in Excel Developer tab
Protect and Unprotect a Sheet in Excel - Developer tab
  1. 4. The VBA Editor should show up automatically, as illustrated below.
Protect and Unprotect a Sheet in Excel VBA Editor
Protect and Unprotect a Sheet in Excel - VBA Editor
How to Password Protect a Google Sheet
How to Password-Protect a Google Sheet?

If you work with important data in Google Sheets, you probably want an extra layer of protection. Here's how you can password protect a Google Sheet

READ MORE

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.
Protect and Unprotect a Sheet in Excel Enter code
Protect and Unprotect a Sheet in Excel - Enter 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.
Protect and Unprotect a Sheet in Excel Save As
Protect and Unprotect a Sheet in Excel - Save As…
  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”.
Protect and Unprotect a Sheet in Excel Click No
Protect and Unprotect a Sheet in Excel - 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.
Protect and Unprotect a Sheet in Excel Click Save
Protect and Unprotect a Sheet in Excel - Click Save
  1. 6. You can now check if your sheet is protected by heading to the “Review” tab. It should now say “Unprotect Sheet”.
Protect and Unprotect a Sheet in Excel Sheet successfully protected
Protect and Unprotect a Sheet in Excel - Sheet successfully protected

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

How To Use Macros In Excel To Automate Tasks?

Macros are a powerful tool that can be used to automate tedious tasks in Excel. Here's how to use Macros in Excel.

READ MORE
How To Use Macros In Excel To Automate Tasks

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
Protect and Unprotect a Sheet in Excel Enter Unprotect code
Protect and Unprotect a Sheet in Excel - Enter Unprotect code
  1. 2. Check to see if your sheet is now unprotected by heading to the “Review” tab. It should now say “Protect Sheet”.
Protect and Unprotect a Sheet in Excel Sheet successfully protected 2
Protect and Unprotect a Sheet in Excel - Sheet successfully protected

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
Protect and Unprotect a Sheet in Excel Password protect VBA code
Protect and Unprotect a Sheet in Excel - Password-protect VBA code
  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.
Protect and Unprotect a Sheet in Excel Edit attempt fail
Protect and Unprotect a Sheet in Excel - Edit attempt fail

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
Protect and Unprotect a Sheet in Excel Enter code to unprotect in VBA
Protect and Unprotect a Sheet in Excel - Enter code to unprotect in VBA
  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.
Protect and Unprotect a Sheet in Excel Edit attempt successful
Protect and Unprotect a Sheet in Excel - Edit attempt successful

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.

How to protect and unprotect Google Sheets?

Layer is an add-on that equips you with the tools to increase efficiency and data quality in your processes on top of Google Sheets. Share parts of your Google Sheets, monitor, review and approve changes, and sync data from different sources – all within seconds. See how it works.

Using Layer, you can:

  • Share & Collaborate: Automate your data collection and validation through user controls.
  • Automate & Schedule: Schedule recurring data collection and distribution tasks.
  • Integrate & Sync: Connect to your tech stack and sync all your data in one place.
  • Visualize & Report: Generate and share reports with real-time data and actionable decisions.

Limited Time Offer: Install the Layer Google Sheets Add-On today and Get Free Access to all the paid features, so you can start managing, automating, and scaling your processes on top of Google Sheets!

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.

Layer google sheets add on offer
Get Started With Layer Today!

Share parts of your Google Sheets, monitor, review and approve changes, and sync data from different sources – all within seconds.

GET STARTED FOR FREE
Hady ElHady
Hady is Content Lead at Layer.

Hady has a passion for tech, marketing, and spreadsheets. Besides his Computer Science degree, he has vast experience in developing, launching, and scaling content marketing processes at SaaS startups.

Originally published Nov 9 2022, Updated Nov 23 2022