Tip: Other languages are Google-Translated. You can visit the English version of this link.
Log in
x
or
x
x
Register
x

or

How to group and ungroup rows in protected worksheet?

As we all known, in a protected worksheet, there are many limitations for us to apply some operations. Such as, we can’t toggle between grouped and ungrouped data. Is there a way to group or ungroup rows in a protected worksheet?

Group and ungroup rows in a protected worksheet with VBA code

Office Tab Enable Tabbed Editing and Browsing in Office, and Make Your Work Much Easier...
Kutools for Excel Solves Most of Your Problems, and Increases Your Productivity by 80%
  • Reuse Anything: Add the most used or complex formulas, charts and anything else to your favorites, and quickly reuse them in the future.
  • More than 20 text features: Extract Number from Text String; Extract or Remove Part of Texts; Convert Numbers and Currencies to English Words.
  • Merge Tools: Multiple Workbooks and Sheets into One; Merge Multiple Cells/Rows/Columns Without Losing Data; Merge Duplicate Rows and Sum.
  • Split Tools: Split Data into Multiple Sheets Based on Value; One Workbook to Multiple Excel, PDF or CSV Files; One Column to Multiple Columns.
  • Paste Skipping Hidden/Filtered Rows; Count And Sum by Background Color; Send Personalized Emails to Multiple Recipients in Bulk.
  • Super Filter: Create advanced filter schemes and apply to any sheets; Sort by week, day, frequency and more; Filter by bold, formulas, comment...
  • More than 300 powerful features; Works with Office 2007-2019 and 365; Supports all languages; Easy deploying in your enterprise or organization.

arrow blue right bubble Group and ungroup rows in a protected worksheet with VBA code


Maybe, there is no other good way to solve this problem but using a VBA code, please do as follows:

1. Activate your worksheet that you want to use, please make sure the worksheet is not protected yet.

2. Then hold down the ALT + F11 keys, and it opens the Microsoft Visual Basic for Applications window.

3. Click Insert > Module, and paste the following code in the Module Window.

VBA code: Group and ungroup rows in a protected worksheet

Sub EnableOutlining()
'Update 20140603
Dim xWs As Worksheet
Set xWs = Application.ActiveSheet
Dim xPws As String
xPws = Application.InputBox("Password:", xTitleId, "", Type:=2)
xWs.Protect Password:=xPws, Userinterfaceonly:=True
xWs.EnableOutlining = True
End Sub

4. Then press F5 key to run this code, and a prompt box will pop out to remind you entering the password to protect the current worksheet. See screenshot:

doc-group-in-protected-sheet1

5. Then click OK, your worksheet has been protected, but you can expand and contract the outline symbols in this protected worksheet, see screenshot:

doc-group-in-protected-sheet1

Note: If your worksheet is protected already, this code will not work.


Kutools for Excel Solves Most of Your Problems, and Increases Your Productivity by 80%

  • Reuse: Quickly insert complex formulas, charts and anything that you have used before; Encrypt Cells with password; Create Mailing List and send emails...
  • Super Formula Bar (easily edit multiple lines of text and formula); Reading Layout (easily read and edit large numbers of cells); Paste to Filtered Range...
  • Merge Cells/Rows/Columns without losing Data; Split Cells Content; Combine Duplicate Rows/Columns... Prevent Duplicate Cells; Compare Ranges...
  • Select Duplicate or Unique Rows; Select Blank Rows (all cells are empty); Super Find and Fuzzy Find in Many Workbooks; Random Select...
  • Exact Copy Multiple Cells without changing formula reference; Auto Create References to Multiple Sheets; Insert Bullets, Check Boxes and more...
  • Extract Text, Add Text, Remove by Position, Remove Space; Create and Print Paging Subtotals; Convert Between Cells Content and Comments...
  • Super Filter (save and apply filter schemes to other sheets); Advanced Sort by month/week/day, frequency and more; Special Filter by bold, italic...
  • Combine Workbooks and WorkSheets; Merge Tables based on key columns; Split Data into Multiple Sheets; Batch Convert xls, xlsx and PDF...
  • More than 300 powerful features. Supports Office/Excel 2007-2019 and 365. Supports all languages. Easy deploying in your enterprise or organization. Full features 30-day free trial.
kte tab 201905

Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier

  • Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
  • Open and create multiple documents in new tabs of the same window, rather than in new windows.
  • Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
officetab bottom
Say something here...
symbols left.
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.
  • To post as a guest, your comment is unpublished.
    Dean Fabella · 8 months ago
    How To Group And Ungroup Rows and Columns In Protected Worksheet?
  • To post as a guest, your comment is unpublished.
    Jorge · 10 months ago
    formidable
  • To post as a guest, your comment is unpublished.
    EddieYeah · 11 months ago
    Someone might need this, I think I figured out how to make this work.

    First, your code needs to be written in "ThisWorkbook" under Microsoft Excel Objects, as @peachyclean suggests.
    Second, take the code that @Sravanthi wrote, and paste to the above mentioned location.

    Sub Workbook_Open()
    'Update 20140603
    Dim xWs As Worksheet
    Set xWs = Application.ActiveSheet
    Dim xPws As String
    xPws = "rfc" ''Application.InputBox("Password:", xTitleId, "", Type:=2)
    xWs.Protect Password:=xPws, Userinterfaceonly:=True
    xWs.EnableOutlining = True
    End Sub

    The thing is that you need to be on the sheet which you want to protect but allowing using grouping, and save the workbook and close, without protecting. Now if you open it, the macro starts automatically, it will make the sheet protected with the password "rfc". Now you can use the grouping, the sheet is protected.

    For my solution, I've modified the password applied, so you can rewrite any password HERE:
    xPws = "WRITEANYPASSWORDHERE" ''Application.InputBox("Password:", xTitleId, "", Type:=2)

    Furthermore, I didn't want the to-be-protected sheet active when opening the file, therefore I've modified this part:
    Set xWs = Application.ActiveSheet ->
    Set xWs = Application.Worksheets("WRITEANYSHEET'SNAMEHERE")

    Now it works like charm, sheet named 'WRITEANYSHEET'SNAMEHERE' is protected but the grouping applicable. On the long run, I think the problem will be that if I want to modify this file and keep the solution, I need to unprotect this sheet to make it work on the next opening. I guess you can write another macro to automatically unprotect when closing :)


    I hope it helped.
  • To post as a guest, your comment is unpublished.
    Denise · 1 years ago
    do you have visuals for the VBA Code discussed 6 days ago to peachyclean about ThisWorkbook under Microsoft Objects instead of a new module. The functionality is lost when I go back into my workbook
  • To post as a guest, your comment is unpublished.
    peachyclean · 1 years ago
    To fix the issue of this not working in your file after you've closed it and opened it again, you have to paste the VBA code in "ThisWorkbook" under Microsoft Excel Objects instead of a new module. This will then automatically run the macro every time the file is opened.
  • To post as a guest, your comment is unpublished.
    Susan · 1 years ago
    I have gotten this code to work. But when I close and reopen I must go to the developer tab, select the macros button, select run and enter the password.

    Is there a way to remove the password from the code OR a auto run code that will automatically run the this marco and enter the password?
  • To post as a guest, your comment is unpublished.
    Sravanthi · 1 years ago
    Sub Workbook_Open()
    'Update 20140603
    Dim xWs As Worksheet
    Set xWs = Application.ActiveSheet
    Dim xPws As String
    xPws = "rfc" ''Application.InputBox("Password:", xTitleId, "", Type:=2)
    xWs.Protect Password:=xPws, Userinterfaceonly:=True
    xWs.EnableOutlining = True
    End Sub
  • To post as a guest, your comment is unpublished.
    Neelash · 1 years ago
    this works for a bit, once you close and reopen, it stops :(
  • To post as a guest, your comment is unpublished.
    Arav · 2 years ago
    When i use the command, i see a error message as shown below:
    Private Sub Workbook_Open()
    Dim wsh As Variant
    For Each wsh In Worksheets(Array("TD_ phase_3", "RS_Phase_2"))
    wsh.EnableOutlining = True
    wsh.Protect Password:="260615", DrawingObjects:=False, _
    contents:=True, _
    Scenarios:=True, _
    AllowFiltering:=True, _
    AllowFormattingCells:=True, _
    userinterfaceonly:=True
    Next wsh
    End Sub
    Run time error '9':
    Subscript out of range
  • To post as a guest, your comment is unpublished.
    Tom · 2 years ago
    How can I change the password to another value?
  • To post as a guest, your comment is unpublished.
    Tom · 2 years ago
    Has it been asked / answered? Where in the code can you specify / change a personal password?
  • To post as a guest, your comment is unpublished.
    Emmanuel Nyemah · 4 years ago
    Hello, please help me expand and collapse rolls and collumns in excel spreadsheet that is protected. I tried using the ones you showed above but they do not work.
  • To post as a guest, your comment is unpublished.
    Emmanuel Nyemah · 4 years ago
    Please help me, I want to collapse and expand some rolls and collumns in an excel spreadsheet that is protected. How can I use macros to do this? I have tried what you showed but they just don't work on my spreadsheet. Please help.
  • To post as a guest, your comment is unpublished.
    Steph · 4 years ago
    Hello! I used the first macro with success and then had the same issue of closing the workbook and the macro no longer working. I see the solution above but cannot get that to work at all. Would you mind stepping me through? Do I combine both codes or just use the latter? If my password is "dog" do I replace one of the values in the code? I am only applying to one worksheet ("Sheet1"); do I use that anywhere? Many thanks in advance!!
  • To post as a guest, your comment is unpublished.
    Chiu · 4 years ago
    Private Sub Workbook_Open()
    Dim wsh As Variant
    For Each wsh In Worksheets(Array("TD_ phase_3", "RS_Phase_2"))
    wsh.EnableOutlining = True
    wsh.Protect Password:="260615", DrawingObjects:=False, _
    contents:=True, _
    Scenarios:=True, _
    AllowFiltering:=True, _
    AllowFormattingCells:=True, _
    userinterfaceonly:=True
    Next wsh
    End Sub
    • To post as a guest, your comment is unpublished.
      Josh · 2 years ago
      Can you do a step by step walk thru as to where to put this as they did in the original instructions. Thank you.
    • To post as a guest, your comment is unpublished.
      jgarner · 2 years ago
      Still not sure how this works. Do i make a new module or attach to the one above?
  • To post as a guest, your comment is unpublished.
    Phi Bach · 5 years ago
    I have the same problem when i close the workbook. Any ideas to fix it?
  • To post as a guest, your comment is unpublished.
    Nauman · 5 years ago
    Thank you so much bro this works really nice.Thanks alot
    • To post as a guest, your comment is unpublished.
      jgarner · 2 years ago
      How did you get this to work? I've tried adding it to the VBA above and making a different module but it still doesnt work. Do i need to change any of the codes? like my password i am using or do i need to change the sheet names?
  • To post as a guest, your comment is unpublished.
    mayich · 5 years ago
    This seems to work great, but when I close and re-open the workbook, I run into the same problem - I cannot expand my collapsed groups.
    • To post as a guest, your comment is unpublished.
      Chiu · 4 years ago
      [quote name="mayich"]This seems to work great, but when I close and re-open the workbook, I run into the same problem - I cannot expand my collapsed groups.[/quote]That matter is solved as bellow
      Private Sub Workbook_Open()
      Dim wsh As Variant
      For Each wsh In Worksheets(Array("Sheet1", "Sheet2"))
      wsh.EnableOutlining = True
      wsh.Protect Password:="260615", DrawingObjects:=False, _
      contents:=True, _
      Scenarios:=True, _
      AllowFiltering:=True, _
      AllowFormattingCells:=True, _
      userinterfaceonly:=True
      Next wsh
      End Sub
      • To post as a guest, your comment is unpublished.
        Morne · 3 years ago
        I got the same problem, as soon as I close and re-enter sheet, it doesn't work... please give step by step of where and how to use
        That matter is solved as bellow
        Private Sub Workbook_Open()
        Dim wsh As Variant
        For Each wsh In Worksheets(Arra y("Sheet1", "Sheet2"))
        wsh.EnableOutli ning = True
        wsh.Protect Password:="2606 15", DrawingObjects: =False, _
        contents:=True, _
        Scenarios:=True, _
        AllowFiltering:=True, _
        AllowFormattingCells:=True, _
        userinterfaceonly:=True
        Next wsh
        End Sub
    • To post as a guest, your comment is unpublished.
      Romi · 5 years ago
      I have the same problem, does anyone know how to overcome it.

      Many thanks