Vba worksheet protection in vba in Excel

Like the Workbook protection, worksheet too can be protected to deny any changes to the whole or some part of the sheet.

For example, if a company wants an excel sheet to be filled by employees, without modifying the existing data, then the sheet and the cells can be protected.

In this way, the user will be able to modify only certain section of the worksheet.

It is to be noted that, Worksheet level protection is not intended as a security feature.

It simply prevents users from modifying locked cells within the worksheet.

The following code shows how to protect unprotect worksheet using VBA.

For demonstration, this sheet has been chosen to protect and redo.

  1. Sub protect_sheet()
  2. Dim pwd As String
  3. Dim prsht As Worksheet
  4. Set prsht = Sheets("Q99")
  5. pwd = InputBox("Please enter a password to protect this sheet")
  6. prsht.Protect pwd, True, True
  7. MsgBox "This sheet is password protected"
  8. End Sub

Note that, following the execution of this code, the sheet will be protected with given password.

The password is shown in the Cell S1 for the ease of the user.

Once tested, unprotect the sheet by Right clicking on this tab as shown below.

excel vba worksheet protection in vba

In the window that appears, enter the password.

The screenshot of the editor is as shown below.

excel vba worksheet protection in vba


You can find similar Excel Questions and Answer hereunder

1) The name manager in Excel is very useful to keep an overview and manage all the variable names in Excel. Here how to use the name manager in VBA

2) How can I sort data using VBA?

3) Vba length of an array in Excel

4) How can I check if a file exists in a folder using VBA?

5) Here a explanation about the global seek function in VBA. Goal Seek is another tool under What If analysis that does a unique function as Scenario Manager.

6) How can I protect / unprotect WorkSheet using VBA?

7) How can I add a WorkSheet and name it as required using VBA?

8) How can I hide a sheet completely from users (the sheet should not even appear in Unhide dialog box)?

9) Tables in Excel VBA. How you can create table in VBA and work with them with macros

10) How can I dynamically add a hyperlink using VBA?


Here the previous and next chapter