Custom protect all sheets

[Rudi]

Hi,

Is there a remote way to capture the users choices from the Sheet Protect Dialog box or some trick to getting them another way short of designing an entire form to simulate the dialog and use that?
Say for example I have 50 sheets, and I want all 50 to be protected but with the users choices of columns/rows/formatting from the protect dialog. All the sheets will take on the same protection choices, so the dialog prompts only once, then use those to protect all sheets?

Something like this fake code, just to show what I mean...

Code: Select all

Sub ProtectAll()
Dim sh As Worksheet
    Choices = Application.Dialogs(xlDialogProtectDocument).Show
    For Each sh In Worksheets
        sh.Protect (Choices)
    Next sh
End Sub

[HansV]

Something like this:

Code: Select all

Sub ProtectAll()
    Dim wsh As Worksheet
    Dim blnAllowCells As Boolean
    Dim blnAllowRows As Boolean
    Dim blnAllowColumns As Boolean
    Set wsh = Worksheets(1)
    wsh.Select
    Application.Dialogs(xlDialogProtectDocument).Show
    blnAllowCells = wsh.Protection.AllowFormattingCells
    blnAllowRows = wsh.Protection.AllowFormattingRows
    blnAllowColumns = wsh.Protection.AllowFormattingColumns
    For Each wsh In Worksheets
        wsh.Protect _
            AllowFormattingCells:=blnAllowCells, _
            AllowFormattingRows:=blnAllowRows, _
            AllowFormattingColumns:=blnAllowColumns
    Next wsh
End Sub

You can add other choices of course.

[Rudi]

Wow...
Its this type of option available for other dialogs?

I have always known the dialogs to be "unreachable"? IOW one could not collect selections from the dialog and apply them later, like a font format, to store in a variable the colour selected in the dialog. I have always just shown the dialog after selecting the range/cell to format and the dialog did the rest..but if it exposes its properties to be collected by variables, that opens a whole new world?

TX for the solution BTW!

[HansV]

I didn't use properties of the dialog, but of the worksheet: most aspects of the way the sheet is protected are represented in the Worksheet.Protection object, through the following properties:

AllowDeletingColumns
AllowDeletingRows
AllowFiltering
AllowFormattingCells
AllowFormattingColumns
AllowFormattingRows
AllowInsertingColumns
AllowInsertingHyperlinks
AllowInsertingRows
AllowSorting
AllowUsingPivotTables

[Rudi]

I understand...I think I was just taken by surprise that I overlook what you ACTUALLY did. Quite clear now to see you assigned to the variables the current state of the sheets protection AFTER the dialog had applied the changes. Still its a great "trick" and something new I have learnt.

[HansV]

By the way, although it's not directly relevant to your problem, it is possible to preset some (but not all) of the settings of the built-in dialogs. See Built-In Dialog Box Argument Lists for an overview of the available arguments.

Unfortunately, Microsoft hasn't kept the arguments up-to-date as Excel evolved, so many of the lists are incomplete or incorrect. The xlDialogProtectDocument arguments work only partially, and don't cover features such as formatting cells, rows and columns (those features were introduced only 12 years ago, in Excel 2002...)

A better example is the cell protection dialog. It has arguments locked and hidden. You cannot specify them by name, you have to do it by position.
To display the dialog with both check boxes ticked, you can use

Application.Dialogs(xlDialogCellProtection).Show True, True

S0804.png

And to display the dialog with only the first check box ticked, use

Application.Dialogs(xlDialogCellProtection).Show True, False

S0805.png

[Rudi]

That is a nice to know...providing a bit of control over it.
That list you linked to is also a good reference to bookmark. TX.

I notice that no matter which dialog you select from the extensive list, they all show 30 arguments. Obviously they are simply placeholders for the appropriate arguments one can assign depending on the dialog. TX for this info. Very nice to know!

1.jpg

[HansV]

Yes, you must look up the actual arguments in the Built-In Dialog Argument Lists page. And that's the only documentation available; you have to work out what the arguments mean and do yourself...

[Rudi]

Hi,

One last thing...
Is there a way to capture the password that was typed into the dialog and use it in the code?

Code: Select all

Sub ProtectAll()
    Dim wsh As Worksheet
    Dim blnAllowCells As Boolean
    Dim blnAllowRows As Boolean
    Dim blnAllowColumns As Boolean
    Dim strSuppliedPassword As String
    Set wsh = Worksheets(1)
    wsh.Select
    Application.Dialogs(xlDialogProtectDocument).Show
    'strSuppliedPassword = ??
    blnAllowCells = wsh.Protection.AllowFormattingCells
    blnAllowRows = wsh.Protection.AllowFormattingRows
    blnAllowColumns = wsh.Protection.AllowFormattingColumns
    For Each wsh In Worksheets
        wsh.Protect _
            Password:=strSuppliedPassword, _
            AllowFormattingCells:=blnAllowCells, _
            AllowFormattingRows:=blnAllowRows, _
            AllowFormattingColumns:=blnAllowColumns
    Next wsh
End Sub

[HansV]

Not as far as I know.

[Rudi]

OK. TX
Appreciated.

[BenCasey]

Hi,

One last thing...
Is there a way to capture the password that was typed into the dialog and use it in the code?
[/code]

Just thinking aloud.
Could you perhaps trap the key ASCII value as each key is depressed and then build the pwd from that?
ie. using KeyDown value.

[Rudi]

Interesting thought, but for the minor purpose of this thread I don't think the complexity of this is necessary.