Just wondering if anyone has created a vba code to emulate the 'Protect/Unprotect Workbook' icon yet?
Just need the same function (or similar) to that icon, but with vba.
Thank you!
Excel VBA Course - From Beginner to Expert
200+ Video Lessons
50+ Hours of Video
200+ Excel Guides
Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)
Create 'Protect Workbook' icon in VBA
0
Discussion
Just to be clear, do you want the ability in VBA to protect worksheets (or workbooks) or do you need to create a button on a sheet to run that code?
John_Ru (rep: 6142) Sep 7, '22 at 7:30 am
I need the vba for: 'Protect Workbook = Structure'
I need to lock & unlock the Workbook structure to lock/unlock the tab order, etc.
something similar to Excel's: 'Review > Protect Workbook = Structure'
(I have to write in vba because 'XLS Padlock program' prevents that Excel icon from functioning, so I need to create a vba button instead).
Thanks!
Excelerate (rep: 4) Sep 7, '22 at 10:43 am
Answers
0
Selected Answer
Excelerate
Revised answer following user clarifiication...
I don't know XLS Padlock (nor do iI have time to try it) but it seems it has VBA security setting which might prevent this working...
Suggest you save the attached file which has two simple macros below in Module1 (and buttons on Sheet1):
Public Pwd As String, Conf As String
Sub ProtectFile()
UserForm1.Show
If Pwd <> "" Then
UserForm2.Show
End If
If Pwd = Conf Then ActiveWorkbook.Protect Structure:=True, Windows:=False, Password:=Pwd
MsgBox "Structure protected"
End Sub
Sub UnprotectFile()
Pwd = InputBox("Enter the password", "Protect workbook structure")
ActiveWorkbook.Unprotect Password:=Pwd
MsgBox "File unprotected"
End SubTwo public variables are declared (Pwd and Conf) and the Protect sub uses two UserForms to collect them, password and confirmation password. The key thing here is that the text entered in the UserForms is masked using the PasswordChar property of the TextBox (here I used an asterisk *).
I've just used a simple InputBox (unmasked) in the Unprotect sub and leave you to use a UserForm instead if you like.
If they work, great. If not perhaps someone else will answer who knows XLS Padlock (are you sure that XLS Padlock does not have options to protect your file structure?).
Hope this helps
Discussion
Hello, yes 'XLS Padlock' prevents Excel's 'Protect WB' icon from working (grayed out) which is fine, as I will do it using vba button instead (as I already do with locking sheets, etc.)
Thanks for the vba snippet but as mentioned I need a more elaborate vba method like Excel's own 'Review > Protect Workbook = Structure'
which allows protect/unprotect WB with or without a user-defined password.
Thanks
which allows protect/unprotect WB with or without a user-defined password.
Thanks
Excelerate (rep: 4) Sep 7, '22 at 2:49 pm
Hello Excelerate,
John has provided you code with a password. If you don't want to use a password simply omit the last part of his code.
and
John has provided you code with a password. If you don't want to use a password simply omit the last part of his code.
ActiveWorkbook.Protect Structure:=True, Windows:=Falseand
ActiveWorkbook.Unprotect
WillieD24 (rep: 557) Sep 7, '22 at 3:29 pm
It cannot use a 'fixed' password, it must use a 'user-defined' password.
(It needs to work like Excel's own 'Review > Protect Workbook' icon).
Excelerate (rep: 4) Sep 7, '22 at 4:02 pm
Excelerate, please see my revised Answer and file. That allows you to have a user-defined password.
John_Ru (rep: 6142) Sep 7, '22 at 4:12 pm
Oops- had to correct launch of second UserForm, now reads:
Changed file too.
UserForm2.ShowChanged file too.
John_Ru (rep: 6142) Sep 7, '22 at 4:34 pm
Thanks much better! Is there a way to remove the '1004 runtime error' if wrong password entered?
Thanks
Excelerate (rep: 4) Sep 7, '22 at 5:05 pm
Add the lines in bold below:
(You could do something similar if Pwd <> Conf in the Protect sub.)
Also, I suggest you start the ProtectSub with:
Then, if no first password is entered (the second time say), the structure will still be protected.
Please confirm this all works with "XLS Padlock" and don't forget to mark the Answer as Selected.
Sub UnprotectFile()
On Error Resume Next
Pwd = InputBox("Enter the password", "Protect workbook structure")
ActiveWorkbook.Unprotect Password:=Pwd
If Err.Number = 1004 Then
MsgBox "Password does not match!"
Else
MsgBox "File unprotected"
End If
End Sub(You could do something similar if Pwd <> Conf in the Protect sub.)
Also, I suggest you start the ProtectSub with:
Sub ProtectFile()
'Reset confirming pwd, in case there's a password,
Conf = ""Then, if no first password is entered (the second time say), the structure will still be protected.
Please confirm this all works with "XLS Padlock" and don't forget to mark the Answer as Selected.
John_Ru (rep: 6142) Sep 7, '22 at 5:17 pm
Thanks John - that works nicely!
Excelerate (rep: 4) Sep 7, '22 at 5:44 pm
Great, thanks for selecting my Answer.
Did you see the suggestion I just added to my last discussion point?
Did you see the suggestion I just added to my last discussion point?
John_Ru (rep: 6142) Sep 7, '22 at 5:54 pm
Actually I missed that. I just added it now, and now it works perfectly.
Thanks so much for the flawless answer, really appreciate it!
Excelerate (rep: 4) Sep 7, '22 at 6:10 pm