Consulting

Results 1 to 7 of 7

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

  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

  2. #2
    Moderator VBAX Wizard Aussiebear's Avatar
    Joined
    Dec 2005
    Location
    Queensland
    Posts
    5,142
    Location
    Welcome to VBAX frostybear. Try the following.
    Private Sub workbook_open()
    Dim ws As Worksheet
    For Each ws In ActiveWorkbook.Sheets
        ws.Protect Password:="Test", userinterfaceonly:=True
    ws.EnableOutlining = True
    NextEnd Sub
    Remember To Do the Following....
    Use [Code].... [/Code] tags when posting code to the thread.
    Mark your thread as Solved if satisfied by using the Thread Tools options.
    If posting the same issue to another forum please show the link

  3. #3
    Thanks so much! Is there a way I can add on the other elements too? Allowing filtering, format changes, inserting comments, etc?

  4. #4
    Moderator VBAX Wizard Aussiebear's Avatar
    Joined
    Dec 2005
    Location
    Queensland
    Posts
    5,142
    Location
    Yes. Just add the lines required as per your post #2
    Remember To Do the Following....
    Use [Code].... [/Code] tags when posting code to the thread.
    Mark your thread as Solved if satisfied by using the Thread Tools options.
    If posting the same issue to another forum please show the link

  5. #5
    Thank you so much! I got it working perfectly with the below, HOWEVER I need the workbook this is being used in to be shared, and when it's shared I receive an error upon opening and it does not work/nothing is protected. Is there any way around this to make this code work in a shared workbook?

    Private Sub workbook_open()
    Dim ws As Worksheet
    For Each ws In ActiveWorkbook.Sheets
        ws.Protect Password:="password", userinterfaceonly:=True, _
        AllowFormattingCells:=True, _
                AllowFormattingColumns:=True, _
                DrawingObjects:=False, _
                Contents:=True
        ws.EnableOutlining = True
        ws.EnableAutoFilter = True
    Next
    End Sub

    EDITED TO ADD: My main goal here is to allow collapsing/uncollapsing grouped columns, filtering, and formatting to a protected, multi-sheet, shared workbook. It's fine if the file needs to be unshared to activate the protection via VBA, but really just need the mentioned abilities while it is shared and protected. Is this possible?
    Last edited by frostybear; 12-03-2023 at 12:55 PM.

  6. #6
    Moderator VBAX Wizard Aussiebear's Avatar
    Joined
    Dec 2005
    Location
    Queensland
    Posts
    5,142
    Location
    You need to unprotect the shared workbook first. The code below is a simplified stand alone version.

    Sub Unprotect_Sheet()
        ActiveWorkbook.UnprotectSharing "YourPassword”
    End Sub
    In your case you might like to try

    Private Sub workbook_open()
    Dim ws As Worksheet
    For Each ws In ActiveWorkbook.Sheets
        ws.Protect Password:="password", SharingPassword:= "SharedPassword", userinterfaceonly:=True, _
        AllowFormattingCells:=True, _
                AllowFormattingColumns:=True, _
                DrawingObjects:=False, _
                Contents:=True
        ws.EnableOutlining = True
        ws.EnableAutoFilter = True
    Next End Sub
    Remember To Do the Following....
    Use [Code].... [/Code] tags when posting code to the thread.
    Mark your thread as Solved if satisfied by using the Thread Tools options.
    If posting the same issue to another forum please show the link

  7. #7
    Thank you! I received an error that it isn’t compatible with the version, platform, architecture etc. assuming this means I am out of luck based on the version of excel I have? Also should clarify I meant shared as in sharing via the review tab, not the online method

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
  •