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

Excel VBA copy contents of protected worksheet

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

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)

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

Unprotect worksheet > do something > protect worksheet

Because you cannot manipulate a worksheet while it is protected, when you write a Macro for a protected worksheet, it is best to unprotect a protected worksheet, then run a Macro, finally protect it  back.

Sub proct()
    pwd = "abc123"
    Sheets("Sheet1").Unprotect (Password = pwd)
    'Do something
    Sheets("Sheet1").Protect (Password = pwd)
End Sub