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

50+ Project Management Templates Pack
Excel PowerPoint Word

Advanced Project Plan & Portfolio Template
Excel Template

Business Presentations Templates Pack
PowerPoint Slides

20+ Excel Project Management Pack
Excel Templates

20+ PowerPoint Project Management Pack
PowerPoint Templates

10+ MS Word Project Management Pack
Word Templates


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
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. 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. March 2, 2015 at 10:29 PM - Reply

    Yes, it is possible. Just use the following macro:

  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


Related pages


vba create userformvba declarationshow to do multiple if statements in excelhow to create a column chart in excel 2013pivot tables definitioninterview questions on vb scriptinglearn vbado while loop vba excelexcel vba delete duplicate rowshow to write excel macroinsert row numbers in excelmsdasql connection stringhow to merge cells on excelvba select a worksheetvba excel fileformatvba isblankexcel 2007 merge cellsvba excel basicsinputbox vbaddl dml commandsunprotect excel forgot passwordcode for comboboxcombine sheets in excelsort vba excelfor cycle vbahyperlink formula in excel 2007excel charts and graphs tutorialwizard excelvba environmentms access vba run query with parametersvba clearleft excel vbaupdate access table from excel vbavba xldowndelete duplicates excelvb script excelvba sheet activatehow to combine columns in excel 2007ms excel checkboxinteractive calendar excelvlookup in excel 2007 with example in exceladvanced filtering excel 2010unhide excel columnhow to convert xls to xml formathide duplicates excelautofiltermode vbahlookup excel 2010excel vba activeworkbook.saveassqlconnection exampleoffset meaning in hindishortcut for format cellsdml dllmerging sheets in excelhow to use name manager in excel 2007vba connection string sql serverrename files vbaexcel vba booleanlookup excel 2007worksheet tab in exceleffort estimation template excelusing the vlookup functionrange function vbadownload data mining add in for excel 2013sumif example excelvba advancedadvanced macros in excelvb filecopylocking a spreadsheetvlookup between two workbooksvba 2007vba connect to databasehide sheets in excel vbaexcel shortcut deleteremove duplicates macroit fresher interview questions and answers