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

Get FREE Advanced Excel Exercises with Solutions!

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 to protect 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 3 quick and suitable ways of using Excel VBA to protect workbooks with passwords.


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


Let’s say, we have a dataset that contains information about several Sales Rep of the Armani group. The Delivered and Ordered products and the Rev 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 workbooks with passwords is an easy task. Here’s an overview of the dataset for today’s task.

excel vba protect workbook with password


1. Using VBA Code to Protect an Excel Workbook with Password

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:="Exceldemy@0043"
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 VBA Code, you will be able to protect the Excel sheet named VBA with the password “Exceldemy@0043” which is 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

Read More: Excel VBA: Protect Sheet with Password and Allow Filter


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

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

Step 1:

  • First, according to method 1insert 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:="Exceldemy@0043"
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 VBA Code, you will be able to unprotect the Excel sheet named Protect with the password “Exceldemy@0043” which is given in the below screenshot.

Protect Excel Workbook with Password Using VBA Code with Worksheet Name

Read More: How to Unprotect Excel Sheet with Password Using VBA


3. Protecting All Excel Sheets with Password Using VBA Code

Last but not least, we will protect all Excel sheets with passwords 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 a 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 VBA 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

Read More: Excel VBA to Protect Sheet but Allow to Select Locked Cells


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


Download Practice Workbook

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


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.


Related Articles

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Md. Abdur Rahim Rasel
Md. Abdur Rahim Rasel

MD. ABDUR RAHIM is a marine engineer proficient in Excel and passionate about programming with VBA. He views programming as an efficient means to save time while managing data, handling files, and engaging with the internet. His interests extend to Rhino3D, Maxsurf C++, AutoCAD, Deep Neural Networks, and Machine Learning, reflecting his versatile skill set. He earned a B.Sc in Naval Architecture & Marine Engineering from BUET, and now he has become a content developer, creating technical content... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo