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.

  1. Sub protect_WB()
  2. Dim pwd As String
  3. Dim prsht As Worksheet
  4. Set prsht = Sheets("Q100")
  5. pwd = InputBox("Please enter a password to protect this Workbook")
  6. prsht.Range("S1") = pwd
  7. ActiveWorkbook.Protect Structure:=True, Windows:=False, Password:=pwd
  8. MsgBox "This workbook is password protected"
  9. MsgBox "This workbook will now be unprotected"
  10. ActiveWorkbook.Unprotect Password:=pwd
  11. 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

excel vba workbook protection in vba

 

You can find similar Excel Questions and Answer hereunder

1) How can I loop through all ActiveX checkboxes in WorkSheet and set them to Unchecked status?

2) Vba list all files in a folder in Excel

3) How do you close, save, open files and do other operations in the workbook in VBA

4) How to concatenate strings in vba in Excel

5) How do I add a symbol like Triangle / Inverted Triangle for indicating trends in a cell using VBA?

6) Line break in vba message box in Excel

7) Vba code to password protect workbook in Excel

8) How can I get the count of number of series in a Chart using VBA?

9) How do I restrict user entry in ActiveX Text Box to just numeric values?

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

 

Here the previous and next chapter