none
Protect sheet but allow subtotaling with vba RRS feed

  • Question

  • Hello,

    Need to protect a sheet using VBA, but while everything else remains protected allow subroutines that subtotal to run.

    Presume that's possible, but may take some workarounds?

    Thanks in advance.

     - Mik

    Tuesday, May 31, 2016 1:54 PM

Answers

  • The Protect method of the Worksheet object contains an argument called "UserInterfaceOnly".  Therefore, to protect the user interface but not macros, set it to True.  Here's an example that first checks whether the worksheet is protect.  If so, it sets the UserInterfaceOnly argument to True...

        With Worksheets("Sheet1")
            If .ProtectContents = True Then
                .Protect Password:="Password", userinterfaceonly:=True
            End If
        End If

    Hope this helps!


    Domenic Tamburino Microsoft MVP - Excel xl-central.com - "For Your Microsoft Excel Solutions"

    • Marked as answer by Mikf Tuesday, May 31, 2016 8:47 PM
    Tuesday, May 31, 2016 5:49 PM

All replies

  • The Protect method of the Worksheet object contains an argument called "UserInterfaceOnly".  Therefore, to protect the user interface but not macros, set it to True.  Here's an example that first checks whether the worksheet is protect.  If so, it sets the UserInterfaceOnly argument to True...

        With Worksheets("Sheet1")
            If .ProtectContents = True Then
                .Protect Password:="Password", userinterfaceonly:=True
            End If
        End If

    Hope this helps!


    Domenic Tamburino Microsoft MVP - Excel xl-central.com - "For Your Microsoft Excel Solutions"

    • Marked as answer by Mikf Tuesday, May 31, 2016 8:47 PM
    Tuesday, May 31, 2016 5:49 PM
  • This is great Domenic.

    Succinctly explained and executed.

    THANKS ..!

     - Mik

    Tuesday, May 31, 2016 8:46 PM
  • You're very welcome!

    Cheers!


    Domenic Tamburino Microsoft MVP - Excel xl-central.com - "For Your Microsoft Excel Solutions"

    Tuesday, May 31, 2016 9:26 PM