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:
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:
- Open an excel workbook
- Press Alt+F11 to open VBA Editor
- Insert a Module for Insert Menu
- Copy the above code and Paste in the code window
- Save the file as macro enabled workbook
- 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:
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:
- Open an excel workbook
- Press Alt+F11 to open VBA Editor
- Insert a Module for Insert Menu
- Copy the above code and Paste in the code window
- Save the file as macro enabled workbook
- Press F5 to execute it
What if I forgot the password and want to Unprotect it via VBA? Is that possible? I use Excel 2010.
Yes, it is possible. Just use the following macro:
http://vbaexamples.com/vba-how-to-unlock-protected-excel-sheet-without-password/
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??
i followed your step to unprotected workbook but it’s not successfully. May you give more detail
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!
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
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………………..
Can i protected cell using password?
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 ?
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.