Excel VBA Course
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.)

View Course

Create 'Protect Workbook' icon in VBA

0

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!

Answer
Discuss

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: 3992) 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
Add to Discussion

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 Sub

Two 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

Discuss

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
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.
ActiveWorkbook.Protect Structure:=True, Windows:=False


and

ActiveWorkbook.Unprotect
WillieD24 (rep: 150) 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: 3992) Sep 7, '22 at 4:12 pm
Oops- had to correct launch of second UserForm, now reads:
UserForm2.Show

Changed file too.
John_Ru (rep: 3992) 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:
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: 3992) 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?
John_Ru (rep: 3992) 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
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login