Note: The other languages of the website are Google-translated. Back to English
Inloggen  \/ 
x
or
x
Registreer  \/ 
x

or

Hoe rijen in een beschermd werkblad te groeperen en te degroeperen?

Zoals we allemaal weten, zijn er in een beschermd werkblad veel beperkingen om sommige bewerkingen toe te passen. We kunnen bijvoorbeeld niet schakelen tussen gegroepeerde en niet-gegroepeerde gegevens. Is er een manier om rijen in een beveiligd werkblad te groeperen of de groepering op te heffen?

Groepeer en degroepeer rijen in een beveiligd werkblad met VBA-code

Tabblad Office Bewerking en browsen met tabbladen in Office inschakelen en uw werk veel gemakkelijker maken ...
Kutools voor Excel lost de meeste van uw problemen op en verhoogt uw productiviteit met 80%
  • Hergebruik alles: Voeg de meest gebruikte of complexe formules, grafieken en al het andere toe aan uw favorieten en gebruik ze in de toekomst snel opnieuw.
  • Meer dan 20 tekstfuncties: Nummer uit tekststring halen; Extract of verwijder een deel van teksten; Converteer cijfers en valuta's naar Engelse woorden.
  • Tools samenvoegen: Meerdere werkmappen en bladen in één; Meerdere cellen / rijen / kolommen samenvoegen zonder gegevens te verliezen; Voeg dubbele rijen en som samen.
  • Hulpmiddelen splitsen: Gegevens splitsen in meerdere bladen op basis van waarde; Eén werkmap naar meerdere Excel-, PDF- of CSV-bestanden; Eén kolom naar meerdere kolommen.
  • Plakken overslaan Verborgen / gefilterde rijen; Tel en som op achtergrondkleur; Stuur gepersonaliseerde e-mails in bulk naar meerdere ontvangers.
  • Superfilter: Maak geavanceerde filterschema's en pas deze toe op elk blad Soort per week, dag, frequentie en meer; Filteren door vetgedrukt, formules, commentaar ...
  • Meer dan 300 krachtige functies; Werkt met Office 2007-2019 en 365; Ondersteunt alle talen; Eenvoudig te implementeren in uw onderneming of organisatie.

pijl blauw rechts bel Groepeer en degroepeer rijen in een beveiligd werkblad met VBA-code


Misschien is er geen andere goede manier om dit probleem op te lossen, maar met behulp van een VBA-code doet u het volgende:

1. Activeer uw werkblad dat u wilt gebruiken, zorg ervoor dat het werkblad nog niet beveiligd is.

2. Houd vervolgens de ALT + F11 toetsen, en het opent de Microsoft Visual Basic for Applications-venster.

3. Klikken Invoegen > Moduleen plak de volgende code in het Module Venster.

VBA-code: groepeer en degroepeer rijen in een beveiligd werkblad

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. Druk vervolgens op F5 toets om deze code uit te voeren, en er verschijnt een promptvenster om u eraan te herinneren het wachtwoord in te voeren om het huidige werkblad te beschermen. Zie screenshot:

doc-groep-in-beschermd-blad1

5. Dan klikken OK, uw werkblad is beschermd, maar u kunt de overzichtssymbolen in dit beschermde werkblad uitvouwen en inkrimpen, zie screenshot:

doc-groep-in-beschermd-blad1

Opmerking:: Als uw werkblad al is beveiligd, werkt deze code niet.


De beste tools voor kantoorproductiviteit

Kutools voor Excel lost de meeste van uw problemen op en verhoogt uw productiviteit met 80%

  • visfuik: Snel invoegen complexe formules, grafieken en alles wat je eerder hebt gebruikt; Versleutel cellen met wachtwoord; Maak een mailinglijst en stuur e-mails ...
  • Super Formula-balk (bewerk eenvoudig meerdere regels tekst en formule); Lay-out lezen (gemakkelijk grote aantallen cellen lezen en bewerken); Plakken in gefilterd bereik...
  • Voeg cellen / rijen / kolommen samen zonder gegevens te verliezen; Gespleten cellen inhoud; Combineer dubbele rijen / kolommen... Voorkom dubbele cellen; Vergelijk Ranges...
  • Selecteer Dupliceren of Uniek Rijen; Selecteer lege rijen (alle cellen zijn leeg); Super zoeken en fuzzy zoeken in veel werkboeken; Willekeurige selectie ...
  • Exacte kopie Meerdere cellen zonder de formuleverwijzing te wijzigen; Maak automatisch verwijzingen naar meerdere bladen; Plaats kogels, Selectievakjes en meer ...
  • Extraheer tekst, Tekst toevoegen, Verwijderen op positie, Ruimte verwijderen; Paging-subtotalen maken en afdrukken; Converteren tussen celinhoud en opmerkingen...
  • Super filter (bewaar en pas filterschema's toe op andere bladen); Geavanceerd sorteren per maand / week / dag, frequentie en meer; Speciaal filter door vet, cursief ...
  • Combineer werkmappen en werkbladen; Tabellen samenvoegen op basis van sleutelkolommen; Gegevens splitsen in meerdere bladen; Batch Converteer xls, xlsx en PDF...
  • Meer dan 300 krachtige functies. Ondersteunt Office / Excel 2007-2019 en 365. Ondersteunt alle talen. Eenvoudig te implementeren in uw onderneming of organisatie. Gratis proefperiode van 30 dagen met volledige functies. 60 dagen geld-terug-garantie.
kte tabblad 201905

Office-tabblad Brengt een interface met tabbladen naar Office en maakt uw werk veel gemakkelijker

  • Schakel bewerken en lezen met tabbladen in Word, Excel, PowerPoint in, Publisher, Access, Visio en Project.
  • Open en maak meerdere documenten in nieuwe tabbladen van hetzelfde venster in plaats van in nieuwe vensters.
  • Verhoogt uw productiviteit met 50% en vermindert elke dag honderden muisklikken voor u!
officetab onderkant
Say something here...
symbols left.
You are guest
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.
    Amandine · 10 days ago
    Hello,
    Thanks for these explanations, it works well.
    I have a file with 3 outline symbols.
    I would like to enable the user to group and ungroup rows when clicking on the symbols 1 and 2,
    but to prevent him from ungrouping rows when clicking on the symbol 3.
    Is there any way to achieve this ?
    Thanks in advance
  • To post as a guest, your comment is unpublished.
    chen · 7 months ago
    这样筛选功能不能用啊
  • To post as a guest, your comment is unpublished.
    Belcrai · 1 years ago
    Is there a way to get this to work on a shared workbook? - I need the track changes, Thanks
  • To post as a guest, your comment is unpublished.
    msbnty@gmail.com · 1 years ago
    f*ck, this stole my excel later and changed password privately
  • To post as a guest, your comment is unpublished.
    Dean Fabella · 2 years ago
    How To Group And Ungroup Rows and Columns In Protected Worksheet?
  • To post as a guest, your comment is unpublished.
    Jorge · 2 years ago
    formidable
  • To post as a guest, your comment is unpublished.
    EddieYeah · 2 years 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.
      Bob C · 1 years ago
      This string looked to be exactly what I needed, as I know nothing about VBA. I was able to get this to work initially but as was pointed out, once you close the spreadsheet and reopen it, it no longer works. I tried to write the code in "ThisWorkbook" as noted but I can't figure out how to do that. I can see "ThisWorkbook" but I don't know how to write in it. Every way I see to create a module, it creates a new module in a the separate "Modules" folder, outside of the "Microsoft Excel Objects" folder. Any suggestions on how to put this code in "ThisWorkbook" ?
    • To post as a guest, your comment is unpublished.
      divya monga · 1 years ago
      Hii.. this worked wonders. the only place where i am getting stuck now is that i need to do this for multiple sheets in the workbook. can u pls help with that.
  • To post as a guest, your comment is unpublished.
    Denise · 3 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 · 3 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 · 3 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 · 3 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 · 3 years ago
    this works for a bit, once you close and reopen, it stops :(
    • To post as a guest, your comment is unpublished.
      Naveen Kumar · 1 years ago
      Even for me , Is there any other solution ?

  • To post as a guest, your comment is unpublished.
    Arav · 4 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 · 4 years ago
    How can I change the password to another value?
  • To post as a guest, your comment is unpublished.
    Tom · 4 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 · 5 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 · 5 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 · 6 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 · 6 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 · 4 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 · 4 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 · 7 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 · 7 years ago
    Thank you so much bro this works really nice.Thanks alot
    • To post as a guest, your comment is unpublished.
      jgarner · 4 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 · 7 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 · 6 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 · 5 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 · 7 years ago
      I have the same problem, does anyone know how to overcome it.

      Many thanks
      • To post as a guest, your comment is unpublished.
        ma99ie · 4 months ago
        You need VBA for this, and the end user will need to allow macros for this to work.

        Press Alt+F11 to activate the Visual Basic Editor.

        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



        This code will be executed automatically each time the workbook is opened.