Protecting Excel Worksheet with Excel VBA
We usually share the final end report with the user or reader. When we share the final end report with the user we wish the user will not make any modification or manipulate the end report. In such a scenario it is all about trust, isn’t it? In this article, we will show you how to protect the sheet by using VBA coding. Since we have a built-in tool in excel VBA called “Protect Sheet”, we need not worry about the user’s threat of manipulation of the report. We can protect the sheet using the password in excel which doesn’t allow the user to make any changes to the worksheet data, all they can do is just to read the report.
How to Protect Sheet Using VBA ?
VBA Protecting sheet involves various parameters to supply this is unlike Unprotecting the sheet. Protect is the method available with VBA, let’s look at the syntax of Protect method with a password.
Wow!!! Don’t get intimidated by looking at the syntax of the protected method. Take a look at the explanation of each argument below.
- Worksheet Name: First we need to mention which worksheet we are going to protect.
- Password: We need to enter the password we are using to protect. If we ignore this parameter excel will lock the sheet without password and while unprotecting the sheet it will unprotect without asking any password.
- Note: Remember the password you are giving, because if you forgot then you have to go through various hard ways.
- Drawing Object: If you wish to protect objects in the worksheet then you can pass the argument as TRUE or else FALSE. The default value is TRUE.
- Contents: To protect the contents of the worksheet set the parameter as TRUE or else FALSE. The default value is FALSE. This will protect only locked cells. The default value is TRUE.
- Scenarios: If there are any what-if analysis scenarios we can protect them as well. To protect TRUE or else FALSE. The default value is TRUE.
- User Interface Only: If you want to protect the user interface other than macro then it should be TRUE. If this argument omitted then it will protect both macros and user interface. If you set the argument to TRUE it will protect only user interface only. The default value is FALSE.
- Allow Formatting Cells: If you want to allow the user to format the cell then you can set the parameter to TRUE or else FALSE. The default value is FALSE.
- Allow Formatting Columns: If you want to allow the user to format any column in the protected sheet then you can set the parameter to TRUE or else FALSE. The default value is FALSE.
- Allow Formatting Rows: If you want to allow the user to format any row in the protected sheet then you can set the parameter to TRUE or else FALSE. The default value is FALSE.
- Allow Insert Columns: If you wish to allow the user to insert new columns then you need to set this to TRUE. The default value is FALSE.
- Allow Insert Rows: If you wish to allow the user to insert new rows then you need to set this to TRUE. The default value is FALSE.
- Allow Insert Hyperlinks: If you wish to allow the user to insert hyperlinks then you need to set this to TRUE. The default value is FALSE.
- Allow Deleting Columns: If you wish to allow the user to delete columns then you need to set this to TRUE. The default value is FALSE.
- Allow Deleting Rows: If you wish to allow the user to delete rows then you need to set this to TRUE. The default value is FALSE.
- Allow Sorting: If you wish to allow the user to sort the data then you need to set this to TRUE. The default value is FALSE.
- Allow Filtering: If you wish to allow the user to filter the data then you need to set this to TRUE. The default value is FALSE.
- Allow Using Pivot Tables: If you wish to allow the user to use pivot tables then you need to set this to TRUE. The default value is FALSE.
Ok, this is the explanation of the syntax of Protect method in VBA. Now we will see some examples.
Example of Protect Sheet Using Excel VBA Coding & password
To protect the sheet the first step is to decide upon which sheet we need to protect using VBA password and we need to call the sheet by its name by using Worksheet Object.
4.6 (247 ratings)
For example assume you want to protect the sheet named as “Master Sheet” then you need to mention the worksheet name like the below.
After mentioning the worksheet name put a dot, but we don’t see any intellisense list to work with, this makes the job difficult. To get access to the intellisense list defines the variable as a worksheet.
Code:
Sub Protect_Example1() Dim Ws As Worksheet End Sub
Now set the worksheet reference to the variable as Worksheets(“Master Sheet”).
Code:
Sub Protect_Example1() Dim Ws As Worksheet Set Ws = Worksheets("Master Sheet") End Sub
Now the variable “Ws” hold the reference of the worksheet named as “Master Sheet”. By using this variable we can access the intellisense list.
Select “Protect” method from the intellisense list.
Specify the password in double quotes.
Code:
Sub Protect_Example1() Dim Ws As Worksheet Set Ws = Worksheets("Master Sheet") Ws.Protect Password:="MyPassword" End Sub
Run the code manually or using the shortcut key F5 then, it will protect the sheet named as “Master Sheet”.
When the sheet is protected, if we want to do any modification then, it shows some error message as shown below.
In case if you wish to protect more than one sheet then we need to use loops, below is the example code to protect the sheet.
Sub Protect_Example2() Dim Ws As Worksheet For Each Ws In ActiveWorkbook.Worksheets Ws.Protect Password:="My Passw0rd" Next Ws End Sub
Recommended Articles
This has been a guide to VBA Protect Sheet. Here we will learn how to use Protect Method in VBA to Protect or lock a sheet using a password in excel along with practical examples and a downloadable template. Below you can find some useful excel VBA articles –
- Examples of VBA Do Loop
- VBA DoEvents Examples
- VBA COUNTA Function
- How to Rename Sheet in VBA
- Activate a Sheet with VBA Code
- Use WorksheetFunction in VBA
- Worksheet Objects in Excel VBA
- 3 Courses
- 12 Hands-on Projects
- 43+ Hours
- Full Lifetime Access
- Certificate of Completion
