Excel VBA: Protect Workbook with Password (3 Quick Tricks)

While working with Microsoft Excel, sometimes we need to protect our dataset for the convenience of our work. Basically, we may need to protect selected cells for two reasons. One is to lock cells for protecting them from further edits or changes whereas the other reason is to use the locked cells as absolute cell references. Today, in this article, we’ll learn three quick and suitable ways how Excel VBA protect workbook with password effectively with appropriate illustrations.


Excel VBA to Protect Workbook with Password (Quick View)

Sub Protect_All_Excel_Sheets()
    Dim x As Worksheet
    For Each x In Worksheets
        x.Protect "Exceldemy"
    Next x
End Sub

excel vba protect workbook with password


Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.


3 Suitable Ways to Protect Workbook with Password Using VBA

Let’s say, we have a dataset that contains information about several sales representatives of the Armani group. The delivery and ordered product and the revenue earned by the sales representatives are given in columns D, C, and E respectively. We will protect our dataset by applying the VBA macros. Using VBA code to protect Excel workbook with passwords is an easy task. Here’s an overview of the dataset for today’s task.

excel vba protect workbook with password


1. Perform a VBA Code to Protect Single Workbook with Password in Excel

Now I’ll show how to protect an Excel sheet with a password by using a simple VBA code. It’s very helpful for some particular moments. From our dataset, we will protect an Excel sheet with a password. Let’s follow the instructions below to protect an Excel sheet with a password!

Step 1:

  • First of all, open a Module, to do that, firstly, from your Developer tab, go to,

Developer → Visual Basic

Perform a VBA Code to Protect Single Workbook with Password in Excel

  • After clicking on the Visual Basic ribbon, a window named Microsoft Visual Basic for Applications – Protect Workbook will instantly appear in front of you. From that window, we will insert a module for applying our VBA code. To do that, go to,

Insert → Module

Step 2:

  • Hence, the Protect Workbook module pops up. In the Protect Workbook module, write down the below VBA
Sub Protect_Excel_Sheet()
Sheets("VBA").Protect Password:="[email protected]"
End Sub

Perform a VBA Code to Protect Single Workbook with Password in Excel

  • Hence, run the VBA To do that, go to,

Run → Run Sub/UserForm

  • After running the VAB Code, you will be able to protect the Excel sheet named VBA with the password [email protected] which has been given in the below screenshot.

Perform a VBA Code to Protect Single Workbook with Password in Excel

Step 3:

  • Now, we will check whether our protected Excel sheet will work or not. To do that, select cell B7.

  • Hence, write down anything in the selected cell, if you get a warning message named Microsoft Excel that appears in front of you then you will be able to protect the Excel sheet which has been given in the below screenshot.

Perform a VBA Code to Protect Single Workbook with Password in Excel


2. Protect Excel Workbook with Password Using VBA Code with Worksheet Name

In this method, we will protect the Excel workbook with a password using VBA code. From our dataset, we will protect the Excel workbook named Protect with the password “[email protected]“. Let’s follow the instructions below to unprotect the Excel workbook with a password!

Step 1:

  • First, according to method 1, insert a new module and type the below VBA code to unprotect the Excel sheet with a password. The VBA code is,
Sub Protect_Excel_Workbook_with_Password()
   Dim R As Worksheet
   Set R = Worksheets("Protect")
   R.Protect Password:="[email protected]"
End Sub

Protect Excel Workbook with Password Using VBA Code with Worksheet Name

  • Further, run the VBA To do that, go to,

Run → Run Sub/UserForm

Step 2:

  • After running the VAB Code, you will be able to unprotect the Excel sheet named Protect with the password [email protected] which has been given in the below screenshot.

Protect Excel Workbook with Password Using VBA Code with Worksheet Name


3. Protect All Excel Sheets with Password Using VBA Code

Last but not the least, we will protect all Excel sheets with password by using a simple VBA code. This is an easy task and time-saving also. Let’s follow the instructions below to protect all Excel sheets with password!

Step 1:

  • First, insert a new module according to method 1 and type the below VBA code to change the font color. The VBA code is,
Sub Protect_All_Excel_Sheets()
    Dim x As Worksheet
    For Each x In Worksheets
        x.Protect "Exceldemy"
    Next x
End Sub

excel vba protect workbook with password

  • Further, run the VBA To do that, go to,

Run → Run Sub/UserForm

Step 2:

  • After running the VAB Code, you will be able to protect all Excel sheets.
  • Now, we will check whether our protected Excel sheet will work or not. To do that, select cell C10 in any of the Excel sheets.

Protect All Excel Sheets Using VBA Code

  • Hence, write down anything in the selected cell, if you get a warning message named Microsoft Excel that appears in front of you then you will be able to protect the Excel sheet which has been given in the below screenshot.

Protect All Excel Sheets Using VBA Code


Things to Remember

👉 You can pop up Microsoft Visual Basic for Applications window by pressing Alt + F11 simultaneously.

👉 If a Developer tab is not visible in your ribbon, you can make it visible. To do that, go to,

File → Option → Customize Ribbon


Conclusion

I hope all of the suitable methods mentioned above to protect the Excel workbook with passwords with VBA code will now provoke you to apply them in your Excel spreadsheets with more productivity. You are most welcome to feel free to comment if you have any questions or queries.

Md. Abdur Rahim Rasel

Md. Abdur Rahim Rasel

Hi! I'm Md. Abdur Rahim Rasel. Welcome to my Profile. Currently, I am working and doing research on Microsoft Excel and here I will be posting articles related to this. I have completed my graduation in Naval Architecture and Marine Engineering(NAME) from Bangladesh University of Engineering and Technology(BUET). I have a passion for learning new things with my knowledge through perseverance and hard work.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo