none
enable outline/grouping on protected excel sheet RRS feed

  • Question

  • Dear Helper,

    I have created an excel file, grouped some of the columns and protected the sheet and the worksheet.

    Once protected, I cannot use the grouping option.

    I have read a few posts and entered the code I found online:

    Double-click ThisWorkbook, under Microsoft Excel Objects in the project explorer on the left hand side.

    Copy the following code into the module that appears:

     

    Private Sub Workbook_Open()
        With Worksheets("Emp Summary")
            .EnableOutlining = True
            .Protect UserInterfaceOnly:=True
        End With
    End Sub

    Is it the right one?

    I am a newbie to VBA and I can't get it to work. :)
    Do I have to use subtotal? If yes, how?

    When I click on Developer and then macro, do I have to enter any details there?

    The macro security I have on is "disable all macros except digitally signed macros"

    I would be very grateful if any of You could help.

    Possible step by step :))

    Many thanks,

    Antonella

    Monday, January 16, 2017 12:13 PM

All replies

  • Hi Anto2256,

    I can see that you only apply the protection on user interface. it means that you can make changes from the code in the sheet.

    to allow the formatting , filtering and sorting you need to specify the parameters for that and set them "True".

    below is the syntax with all the parameters. you just need to pass which you want to use others are optional.

    expression .Protect(Password, DrawingObjects, Contents, Scenarios, UserInterfaceOnly, AllowFormattingCells, AllowFormattingColumns, AllowFormattingRows, AllowInsertingColumns, AllowInsertingRows, AllowInsertingHyperlinks, AllowDeletingColumns, AllowDeletingRows, AllowSorting, AllowFiltering, AllowUsingPivotTables)

    Reference:

    Worksheet.Protect Method (Excel)

    you had mentioned that,"The macro security I have on is "disable all macros except digitally signed macros"".

    if you want to keep use this setting then you need to sign your code.

    so to add the Digital Signature follow the steps below.

    Open VBE -> Click on "Tools" Tab -> click on "Digital Signature". then add signature.

    below link is for word but you have to follow the same steps in Excel.

    Code Signing with Microsoft Office Visual Basic (VBA)

    if you don't want to add digital signature then just change the below settings in Excel.

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, January 17, 2017 3:19 AM
    Moderator