Protect and Unprotect Excel Workbook using VBA

Home/Excel VBA/Protect and Unprotect Excel Workbook using VBA

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

Business Presentations Templates Pack
PowerPoint Slides

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
LIMITED TIME OFFER - Get it Now!
Business PowerPoint Presentations Templates Pack
 
 
Related Resource External VBA Reference
By |February 28th, 2013|Excel VBA|10 Comments

About the Author:

Excel VBA Developer having around 8 years of experience in using Excel and VBA for automating the daily tasks, reports generation and dashboards preparation. Valli is sharing to helps us automating daily tasks.

10 Comments

  1. Ueritom December 8, 2014 at 4:17 AM - Reply

    What if I forgot the password and want to Unprotect it via VBA? Is that possible? I use Excel 2010.

  2. Lars March 2, 2015 at 10:29 PM - Reply
  3. JC Amora July 12, 2015 at 8:20 PM - Reply

    Hi sir,

    I worked on a macro empowered sheet trial..
    Problem is I protected the VBA now I forgot what the password was and could not unlock it.
    Its a compendium of my work, and I could not edit or add new modules in the said project.

    Is it possible to unlock it??

  4. Pavteang January 31, 2016 at 8:51 PM - Reply

    i followed your step to unprotected workbook but it’s not successfully. May you give more detail

    • PNRao February 1, 2016 at 11:19 AM - Reply

      Hi,
      This example macro will prompt for workbook password and it will open if you provide the correct password.

      Please let me know your requirement and I will suggest the code accordingly.
      Thanks-PNRao!

  5. patricia February 4, 2016 at 10:27 PM - Reply

    Hello, this forum seems good, I have a 2010 excel workbook that is password protected and needs unlocking, however, the remedies given in here are all for unlocking the ‘sheets’ within a workbook. I need to unlock the actual workbook since when I say file open and select the workbook to open, it asks for the password at that point of entry, without it you cannot even open the workbook itself let alone see or get to any of the sheets, at all. help!!! thnx

  6. Awashesh Tiwari July 3, 2016 at 5:51 PM - Reply

    Hi, i read somewhere that if we change the file extensiion to zip file and remove password from the notepad which comes after extracting the zip folder. However, when i tried the same, i was not able to open the zip folder. I agree with patricia, is there any way where i can unlock the excel workbook ( Not excel work sheet). Please helpp………………..

  7. Andre October 10, 2016 at 8:37 PM - Reply

    Can i protected cell using password?

  8. Hari October 13, 2016 at 8:05 PM - Reply

    Hi Sir,

    I have an Excel File for which I have forgot the Password. I tried to go as per your instructions but in the Insert Menu, Module Tab is not getting highlighted. Could you please help ?

  9. Sub December 29, 2016 at 3:51 PM - Reply

    I have an excel file which needs to be protected by a password before it is sent. I need the macro to work for a different excel file and not the current file where the code is actually written.
    Also I have made a macro create password based on certain user inputs, but need to use that password to protect the sheet which can be passed as a parameter.

Leave A Comment