This Excel VBA tutorial explains how to use VBA to protect worksheet with password.
You may also want to read:
Different kinds of workbook protection
There are several kinds of protection in Excel.
- Add password to workbook. Users are asked to open the Excel file with password.
- Protect workbook with password. Protect the structure of all worksheets (do not allow insert, delete, rename, move, copy, hide or unhide worksheets)
- Hide worksheet with password. Hide the worksheet and add a password to unhide the worksheet.
- 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
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)
|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 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