View original (French)

Group or Ungroup in a Protected Sheet

Seiken781 Posted messages 2 Registration date   Status Member Last intervention   -  
gbinforme Posted messages 14930 Registration date   Status Contributor Last intervention   -
Hello,

I have protected several Excel sheets (which contain quite a few formulas) and I would like to group and ungroup the data so that I can have the important information for direct reading.

The problem is that when I lock my Excel sheets, it no longer allows me to group or ungroup.

I read that the problem can be circumvented with a macro, but I don't know anything about it at all.

Does anyone have a step-by-step procedure?
Note: I am using Office 2013

Thank you in advance!

1 answer

  1.
    gbinforme Posted messages 14930 Registration date   Status Contributor Last intervention   4 744
     
    Hello,

    I read that the problem could be circumvented via a macro
    You talk about a workaround: the macro removes the protection, performs the action, and then reinstates it. If you want to do something other than what's coded, you'll need to modify the macro. Moreover, if the macro allows it, anyone can use it, so what’s the point of the protection?

    I don’t know the reason for the protection, but I hope it’s mandatory for you because if it’s the first issue it creates, it won’t be the last, especially if you forget the password.
    --
    Always zen
    Perfection is achieved, not when there is nothing more to add, but when there is nothing left to take away. Antoine de Saint-Exupéry
    0
    1. Seiken781 Posted messages 2 Registration date   Status Member Last intervention  
       
      Hello,

      To be more precise about my request: the file will be shared with a number of users and I need to protect many formulas.
      Since the file contains a huge number of rows, I need to be able to group and ungroup to avoid scrolling with the mouse over hundreds of rows.

      The operation I read was as follows. I tried it but it doesn't work on my file.

      With VBA

      Private Sub Workbook_Open()
      With Worksheets("Sheet1")
      .EnableAutoFilter = True
      .EnableOutlining = True
      .Protect Contents:=True, Password:"Toto", UserInterfaceOnly:=True
      End With
      End Sub

      I'm stuck...
      0
    2. gbinforme Posted messages 14930 Registration date   Status Contributor Last intervention   4 744
       
      Hello,

      The code you provided will not allow you to group as you intended. You can modify it as follows: 
      Private Sub Workbook_Open() With Worksheets("Feuil1") .EnableAutoFilter = True .EnableOutlining = True .Protect "1234", , True, , True End With End Sub

      I suggest these two macros to place in a standard module and you can assign them to shortcuts (ctrl+g / ctrl+d for example): to group, select your rows and press ctrl+g, and the same for ungrouping 
      Public Sub grouper() ActiveSheet.Unprotect "1234" Selection.Rows.Group ActiveSheet.EnableOutlining = True ActiveSheet.Protect "1234", , True, , True End Sub Public Sub degrouper() ActiveSheet.Unprotect "1234" Selection.Rows.Ungroup ActiveSheet.EnableOutlining = True ActiveSheet.Protect "1234", , True, , True End Sub
      Just replace 1234 with your password, of course.
      0