Show Unprotect dialog box in Excel VBA

[Les Laplace]

Hello All,

Can anyone help me with the macro code for Excel VBA to show the "Unprotect" dialog box?

When I use "Activesheet Unprotect" the unprotect dialog box to enter the password appears nicely, but when I use "ActiveSheet Protect" the dialog box does not appear allowing the password to be entered. I would have to include the "Password:="xxx" parameter. I would rather not hardcode a password in the macro code.

Appreciate any help.

Thanks,
Les.

 

[Les Laplace]

Sorry, the first sentence should read "Can anyone help me with the macro code for Excel VBA to show the "Protect" dialog box?

 

[Jake Collins]

Dear Les Laplace

I'm not sure that there is a way but a workaround would be to use an inputbox.

 

[Les Laplace]

Thanks for your quick reply Jake. Can you give an example please? I am quite new to VBA.

Thanks again.

 

[vletm]

@Les Laplace
You could try to find from next link Your dialog box ...
XlBuiltInDialog Enumeration
https://msdn.microsoft.com/en-us/library/office/ff194519.aspx

... and sample would be like this
dlgAnswer = Application.Dialogs(xlDialogOpen).Show
https://msdn.microsoft.com/en-us/library/office/ff839409.aspx

 

[shrivallabha]

The path to get information can be convoluted. You can print the list of controls using below code.

Sub ShowProtect()
Dim cmdBar As CommandBar
Dim ctrl As CommandBarControl
i = 1
For Each cmdBar In Application.CommandBars
Cells(i, "A").Value = cmdBar.Name
Cells(i, "B").Value = cmdBar.Index
i = i + 1
'    If cmdBar.Name = "Protection" Then
        For Each ctrl In cmdBar.Controls
            Cells(i, "C").Value = ctrl.Caption
            Cells(i, "D").Value = ctrl.Index
            i = i + 1
        Next ctrl
'    End If
Next cmdBar
End Sub

Once it is printed then you can invoke the correct item using syntax like below. For my computer, following syntax invokes Protect Sheet Dialog box.

Application.CommandBars(31).Controls(3).Execute

 

[Les Laplace]

Wonderful, thanks a million Vletm, excellent information. I delved into it and found the following to show the protect sheet dialog:
Application.Dialogs(xlDialogProtectDocument).Show
Works like a charm.

Thanks.

 

[Les Laplace]

Thanks shrivallabha, will try it out and let you know.


__________________________________________________________________

Mod edit : post moved to appropriate forum​