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
Premium Project Management Templates
120+ Professional Project Management Templates!

A Powerful & Multi-purpose Templates for project management. Now seamlessly manage your projects, tasks, meetings, presentations, teams, customers, stakeholders and time. This page describes all the amazing new features and options that come with our premium templates.

PREMIUM TEMPLATES
LIMITED TIME OFFER
ON SALE85% OFF
BROWSE ALL TEMPLATES

Advanced Project Planning Templates

Excel Templates

VIEW DETAILS

120+ Project Management Templates Pack

Excel | PowerPoint | Word

VIEW DETAILS

ULTIMATE RESOURCE MANAGEMENT TEMPLATE

Excel Template

VIEW DETAILS

50+ Essential Project Management Templates

Excel | PowerPoint | Word

VIEW DETAILS

Project Portfolio Management Templates

Excel | PowerPoint Templates

VIEW DETAILS

50+ Excel Project Management Templates

Excel Templates

VIEW DETAILS

By Published On: February 28th, 2013Categories: Excel VBATags:

Share This Story, Choose Your Platform!

About the Author: Valli

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

    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
  3. JC Amora July 12, 2015 at 8:20 PM

    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

    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

      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

    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

    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

    Can i protected cell using password?

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

    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

    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