Protect Excel Workbook using VBA

When we are dealing with confidential data we need to Protect Workbook in Excel VBA. So that user can enter password if he has and can view the data in the workbook.

Solution:

Protect Workbook in Excel VBA
We can use Protect method of a workbook to protect the workbook form the anonymous users.

Protect Workbook in Excel VBA – Example:

Following is the example to show you how to Protect Workbook in Excel using VBA.

Code:
Sub ProtectWorkbook()
On Error GoTo ErrorOccured
Dim pwd1 As String, ShtName As String
pwd1 = InputBox("Please Enter the password")
If pwd1 = "" Then Exit Sub
ShtName = "Workbook as a whole"
ActiveWorkbook.Protect Structure:=True, Windows:=False, Password:=pwd1
MsgBox "The workbook's structure has been protected."
Exit Sub
ErrorOccured:
MsgBox "Workbook could not be Protected"
Exit Sub
End Sub
Instructions:
  1. Open an excel workbook
  2. Press Alt+F11 to open VBA Editor
  3. Insert a Module for Insert Menu
  4. Copy the above code and Paste in the code window
  5. Save the file as macro enabled workbook
  6. Press F5 to execute it

UnProtect Excel Workbook using VBA

When we are dealing with confidential data we need to Protect Workbook if you want. You may want to UnProtect Workbook in Excel VBA when you feel its not required to protect it.

Solution:

UnProtect Workbook in Excel VBA
We can use UnProtect method of a workbook to UnProtect Workbook in Excel using VBA.

UnProtect Workbook in Excel VBA – Example:

Following is the example to show you how to UnProtect Workbook in Excel using VBA.

Code:
Sub UnProtectWorkbook()
On Error GoTo ErrorOccured
Dim pwd1 As String, ShtName As String
pwd1 = InputBox("Please Enter the password")
If pwd1 = "" Then Exit Sub
ShtName = "Workbook as a whole"
ActiveWorkbook.Unprotect Password:=pwd1
MsgBox "The workbook's structure has been Unprotected."
Exit Sub
ErrorOccured:
MsgBox "Workbook could not be UnProtected - Password Incorrect"
Exit Sub     
End Sub
Instructions:
  1. Open an excel workbook
  2. Press Alt+F11 to open VBA Editor
  3. Insert a Module for Insert Menu
  4. Copy the above code and Paste in the code window
  5. Save the file as macro enabled workbook
  6. Press F5 to execute it

Hey! Join Our Community

Get Quick Responses & Experts' Answers in Minutes!
Get Notified - When Answered Your Question!