REAL-TIME

VBA Projects

Full Access with Source Code

  • Designed and Developed by PNRao

  • Full Access with VBA Source Code

  • Well Commented Codes Lines

  • Creative and Professional Design

120+ PROFESSIONAL

Project Management Templates

120+ PM Templates Includes:
  • 50+ Excel Templates

  • 50+ PowerPoint Templates

  • 25+ Word Templates

Effortlessly Manage Your Projects

Seamlessly manage your projects with our powerful & multi-purpose templates for project management.

Share Post

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
Effortlessly Manage Your Projects and Resources
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.

Save Up to 85% LIMITED TIME OFFER
Project Management Templates

All-in-One Pack
120+ Project Management Templates

Essential Pack
50+ PM Templates

Excel Pack
50+ Excel PM Templates

PowerPoint Pack
50+ Excel PM Templates

MS Word Pack
25+ Word PM Templates

Ultimate Project
Management Template
Ultimate Resource
Management Template
Project Portfolio
Management Templates
Categories: Excel VBATags: Last Updated: June 17, 2022

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

  5. 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!

  6. 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

  7. 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………………..

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

    Can i protected cell using password?

  9. 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 ?

  10. 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