Workbook protection in vba in Excel
Protection in excel means, disallowing the user to make changes to the workbook or in some cases, to open the workbook or worksheet.
This involves setting a password and the modifications, deletions can be done only if the password is known to the end user.
Many organizations official excel documents used by the employees are somehow protected either, a sheet, a cell or even the entire workbook.
The chapter explains how to protect a workbook.
The following code demonstrates the workbook password protection in VBA.
- Sub protect_WB()
- Dim pwd As String
- Dim prsht As Worksheet
- Set prsht = Sheets("Q100")
- pwd = InputBox("Please enter a password to protect this Workbook")
- prsht.Range("S1") = pwd
- ActiveWorkbook.Protect Structure:=True, Windows:=False, Password:=pwd
- MsgBox "This workbook is password protected"
- MsgBox "This workbook will now be unprotected"
- ActiveWorkbook.Unprotect Password:=pwd
- End Sub
The above example protects this workbook with a password as input by the user.
For security purpose, the workbook is unprotected immediately, since loss of password will make this workbook inaccessible.
The screenshot of the editor is as shown below