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.

PREMIUM TEMPLATES LIMITED TIME OFFER

ON SALE80% OFF

BROWSE ALL TEMPLATES

50+ Project Management Templates Pack
Excel PowerPoint Word

VIEW DETAILS

Advanced Project Plan & Portfolio Template
Excel Template

VIEW DETAILS

Ultimate Project Management Template
Excel Template

VIEW DETAILS

20+ Excel Project Management Pack
Excel Templates

VIEW DETAILS

20+ PowerPoint Project Management Pack
PowerPoint Templates

VIEW DETAILS

10+ MS Word Project Management Pack
Word Templates

VIEW DETAILS


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
LIMITED TIME OFFER - Get it Now!
Advanced Project Plan Excel Template

 
Related Resource External VBA Reference