Excel VBA protect worksheet with password

This Excel VBA tutorial explains how to use VBA to protect worksheet with password.

You may also want to read:

Excel VBA hide worksheet with password (xlVeryHidden)

Excel VBA Add password to Excel workbook in folder

Protect VBA code with password

Different kinds of workbook protection

There are several kinds of protection in Excel.

  1. Add password to workbook. Users are asked to open the Excel file with password.
  2. Protect workbook with password. Protect the structure of all worksheets (do not allow insert, delete, rename, move, copy, hide or unhide worksheets)
  3. Hide worksheet with password. Hide the worksheet and add a password to unhide the worksheet.
  4. Protect worksheet (cells) with password. Restrict users to edit the cell

Lock Cell and Unlock Cell

Before doing any worksheet Protection, the first thing to do is to ensure the Cell you want to protect is locked.

Right click on a Cell > Format Cells > Protection > check Locked

protect_worksheet_01

To lock a Cell in VBA

Range("A1").Locked = TRUE

To unlock a Cell in VBA

Range("A1").Locked = FALSE

Similarly, Hidden can also be enabled in VBA

Range("A1").FormulaHidden = TRUE

Protect worksheet

Now the next step is to do the actual protection using Worksheet.Protect Method.

Syntax of Worksheet.Protect

Worksheet.Protect(Password, DrawingObjects, Contents, Scenarios, UserInterfaceOnly, AllowFormattingCells, AllowFormattingColumns, AllowFormattingRows, AllowInsertingColumns, AllowInsertingRows, AllowInsertingHyperlinks, AllowDeletingColumns, AllowDeletingRows, AllowSorting, AllowFiltering, AllowUsingPivotTables)
Name Required/Optional Description
Password Optional A string that specifies a case-sensitive password for the worksheet or workbook. If this argument is omitted, you can unprotect the worksheet or workbook without using a password. Otherwise, you must specify the password to unprotect the worksheet or workbook. If you forget the password, you cannot unprotect the worksheet or workbook.
DrawingObjects Optional True to protect shapes. The default value is True.
Contents Optional True to protect contents. For a chart, this protects the entire chart. For a worksheet, this protects the locked cells. The default value is True.
Scenarios Optional True to protect scenarios. This argument is valid only for worksheets. The default value is True.
UserInterfaceOnly Optional True to protect the user interface, but not macros. If this argument is omitted, protection applies both to macros and to the user interface.
AllowFormattingCells Optional True allows the user to format any cell on a protected worksheet. The default value is False.
AllowFormattingColumns Optional True allows the user to format any column on a protected worksheet. The default value is False.
AllowFormattingRows Optional True allows the user to format any row on a protected. The default value is False.
AllowInsertingColumns Optional True allows the user to insert columns on the protected worksheet. The default value is False.
AllowInsertingRows Optional True allows the user to insert rows on the protected worksheet. The default value is False.
AllowInsertingHyperlinks Optional True allows the user to insert hyperlinks on the worksheet. The default value is False.
AllowDeletingColumns Optional True allows the user to delete columns on the protected worksheet, where every cell in the column to be deleted is unlocked. The default value is False.
AllowDeletingRows Optional True allows the user to delete rows on the protected worksheet, where every cell in the row to be deleted is unlocked. The default value is False.
AllowSorting Optional True allows the user to sort on the protected worksheet. Every cell in the sort range must be unlocked or unprotected. The default value is False.
AllowFiltering Optional True allows the user to set filters on the protected worksheet. Users can change filter criteria but can not enable or disable an auto filter. Users can set filters on an existing auto filter. The default value is False.
AllowUsingPivotTables Optional True allows the user to use pivot table reports on the protected worksheet. The default value is False.

The simplest way to set password is to leave everything default and just set the password.

Sub proct()
    ActiveSheet.Protect (Password = "abc123")
End Sub

To protect all worksheets in workbook

Sub proctAll()
    For Each sht In ActiveWorkbook.Sheets
       sht.Protect (Password = "abc123")
    Next
End Sub

Since everyone can press ALT+F11 to see your password, you should lock the project view of VBE.

Press ALT+F11 > Navigate to Tools > VBAProject Properties > Protection > check the box “Lock project for viewing” > enter password (close the workbook and reopen to take effect)

hide_worksheet_1

Unprotect worksheet

Unprotect is more simple, unprotect Method has only one optional argument – the password.

Sub unproct()
    ActiveSheet.Unprotect (Password = "abc123")
End Sub

If the sheet or workbook isn’t protected with a password, this argument is ignored.

If you omit this argument for a sheet that’s protected with a password, you’ll be prompted for the password.

If you omit this argument for a workbook that’s protected with a password, the method fails.

If you are trying to modify a protected worksheet, you should run Macro to unprotect > modify > protect

 

Wyman W
Wyman is a Business Analyst based in Hong Kong, specialized in creating custom Function and Sub solutions, and is proficient in report automation with Access.

He is also a:
- Microsoft Most Valuable Professional (Excel)
- Microsoft Community Contributor
- Microsoft Office Specialist
- Microsoft Specialist
- Microsoft Technical Associate
- Microsoft Certified Professional
- IBM SPSS Specialist

Leave a Reply

Your email address will not be published. Required fields are marked *