Consulting

Results 1 to 7 of 7

Thread: VBA - protect sheet and allow grouping/ungrouping (outline)

Threaded View

Previous Post Previous Post   Next Post Next Post
  1. #1

    VBA - protect sheet and allow grouping/ungrouping (outline)

    Hi - I have a file that needs to be protected but still allows for collapsing/uncollapsing grouped columns & rows, editing on unlocked cells etc. I have 2 sets of code that almost work, but one does everything except the grouping and the other does everything except the password protection. I'm going crazy!


    Option 1:
    This does everything I need EXCEPT the password protection piece which I could not figure out how to correctly incorporate into this:
    
    Private Sub Workbook_Open()
        Dim wsh As Variant
        For Each wsh In Worksheets(Array("sheet 1", "sheet 2"))
            wsh.EnableOutlining = True
            wsh.EnableAutoFilter = True
            wsh.Protect UserInterfaceOnly:=True, _
                AllowFormattingCells:=True, _
                AllowFormattingColumns:=True, _
                DrawingObjects:=False, _
                Contents:=True
        Next wsh
    End Sub


    Option 2:
    This one does everything I need EXCEPT the ability to collapse/uncollapse grouped columns/rows:

    Sub ProtectSheets()
        Dim wsh As Variant
        For Each wsh In Worksheets(Array("sheet 1", "sheet 2"))
            wsh.Protect Password = "password"
            wsh.EnableOutlining = True
            wsh.EnableAutoFilter = True
            wsh.Protect UserInterfaceOnly:=True, _
                AllowFormattingCells:=True, _
                AllowFormattingColumns:=True, _
                DrawingObjects:=False, _
                Contents:=True
        Next wsh
    End Sub
    Hopefully this is an easy fix to get everything I need - I am definitely not an expert on this Thank you!
    Last edited by Aussiebear; 11-30-2023 at 04:37 PM. Reason: Added code tags to supplied code

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •