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

or

Comment grouper et dissocier des lignes dans une feuille de calcul protégée?

Comme nous le savons tous, dans une feuille de calcul protégée, il existe de nombreuses limitations pour appliquer certaines opérations. Par exemple, nous ne pouvons pas basculer entre les données groupées et non groupées. Existe-t-il un moyen de regrouper ou de dissocier des lignes dans une feuille de calcul protégée?

Regrouper et dissocier des lignes dans une feuille de calcul protégée avec le code VBA

Onglet Office Activez la modification et la navigation par onglets dans Office et simplifiez considérablement votre travail ...
Kutools for Excel résout la plupart de vos problèmes et augmente votre productivité de 80%
  • Réutiliser n'importe quoi: Ajoutez les formules, les graphiques et tout autre élément les plus utilisés ou les plus complexes à vos favoris et réutilisez-les rapidement à l'avenir.
  • Plus de 20 fonctionnalités de texte: Extraire le numéro de la chaîne de texte; Extraire ou supprimer une partie des textes; Convertissez les nombres et les devises en mots anglais.
  • Outils de fusion: Plusieurs classeurs et feuilles en un seul; Fusionner plusieurs cellules / lignes / colonnes sans perdre de données; Fusionner les lignes en double et la somme.
  • Outils de fractionnement: Diviser les données en plusieurs feuilles en fonction de la valeur; Un classeur vers plusieurs fichiers Excel, PDF ou CSV; Une colonne à plusieurs colonnes.
  • Coller sauter Lignes cachées / filtrées; Compte et somme par couleur d'arrière-plan; Envoyez des e-mails personnalisés à plusieurs destinataires en masse.
  • Super filtre: Créez des schémas de filtres avancés et appliquez-les à toutes les feuilles; Trier par semaine, jour, fréquence et plus encore; Filtre par gras, formules, commentaire ...
  • Plus de 300 fonctionnalités puissantes; Fonctionne avec Office 2007-2019 et 365; Prend en charge toutes les langues; Déploiement facile dans votre entreprise ou organisation.

flèche bleue bulle droite Regrouper et dissocier des lignes dans une feuille de calcul protégée avec le code VBA


Peut-être qu'il n'y a pas d'autre moyen de résoudre ce problème, mais en utilisant un code VBA, procédez comme suit:

1. Activez votre feuille de calcul que vous souhaitez utiliser, veuillez vous assurer que la feuille de calcul n'est pas encore protégée.

2. Puis maintenez le ALT + F11 clés, et il ouvre le Fenêtre Microsoft Visual Basic pour Applications.

3. Cliquez insérer > Moduleet collez le code suivant dans le Fenêtre du module.

Code VBA: regrouper et dissocier des lignes dans une feuille de calcul protégée

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. Puis appuyez F5 clé pour exécuter ce code, et une boîte de dialogue apparaîtra pour vous rappeler d'entrer le mot de passe pour protéger la feuille de calcul actuelle. Voir la capture d'écran:

doc-group-in-protected-sheet1

5. Puis clique OK, votre feuille de calcul a été protégée, mais vous pouvez développer et réduire les symboles de contour dans cette feuille de calcul protégée, voir capture d'écran:

doc-group-in-protected-sheet1

Notes: Si votre feuille de calcul est déjà protégée, ce code ne fonctionnera pas.


Les meilleurs outils de productivité de bureau

Kutools for Excel résout la plupart de vos problèmes et augmente votre productivité de 80%

  • Réutilisation: Insérer rapidement formules complexes, graphiques et tout ce que vous avez utilisé auparavant; Crypter les cellules avec mot de passe; Créer une liste de diffusion et envoyer des e-mails ...
  • Barre Super Formula (modifiez facilement plusieurs lignes de texte et de formule); Disposition de lecture (lire et modifier facilement un grand nombre de cellules); Coller dans la plage filtrée...
  • Fusionner les cellules / lignes / colonnes sans perdre de données; Contenu des cellules divisées; Combiner des lignes / colonnes en double... Empêcher les cellules en double; Comparer les gammes...
  • Sélectionnez Dupliquer ou Unique Lignes; Sélectionnez les lignes vides (toutes les cellules sont vides); Super Find et Fuzzy Find dans de nombreux classeurs; Sélection aléatoire ...
  • Copie exacte Plusieurs cellules sans changer la référence de formule; Créer automatiquement des références à plusieurs feuilles; Insérer des puces, Cases à cocher et plus encore ...
  • Extrait du texte, Ajouter du texte, Supprimer par position, Supprimer l'espace; Créer et imprimer des sous-totaux de pagination; Conversion entre le contenu et les commentaires des cellules...
  • Super filtre (enregistrer et appliquer des schémas de filtrage à d'autres feuilles); Tri avancé par mois / semaine / jour, fréquence et plus; Filtre spécial par gras, italique ...
  • Combiner des classeurs et des feuilles de travail; Fusionner les tableaux en fonction des colonnes clés; Diviser les données en plusieurs feuilles; Conversion par lots xls, xlsx et PDF...
  • Plus de 300 fonctionnalités puissantes. Prend en charge Office / Excel 2007-2019 et 365. Prend en charge toutes les langues. Déploiement facile dans votre entreprise ou organisation. Essai gratuit de 30 jours. Garantie de remboursement de 60 jours.
onglet kte 201905

Office Tab apporte une interface à onglets à Office et simplifie considérablement votre travail

  • Activer l'édition et la lecture par onglets dans Word, Excel, PowerPoint, Publisher, Access, Visio et Project.
  • Ouvrez et créez plusieurs documents dans de nouveaux onglets de la même fenêtre, plutôt que dans de nouvelles fenêtres.
  • Augmente votre productivité de 50% et réduit des centaines de clics de souris chaque jour!
bas de cabine
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 · 1 months 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 · 8 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 · 5 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.