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

or

Comment garder le tableau extensible en insérant une ligne de tableau dans une feuille de calcul protégée dans Excel?

La fonction d'expansion automatique du tableau sera perdue après la protection de la feuille de calcul dans Excel. Par exemple, il existe une table nommée Table1 dans votre feuille de calcul protégée, lorsque vous tapez quelque chose sous la dernière ligne, la table ne se développe pas automatiquement pour inclure la nouvelle ligne. Existe-t-il une méthode pour garder le tableau extensible en insérant une nouvelle ligne dans une feuille de calcul protégée? La méthode décrite dans cet article peut vous aider à y parvenir.

Gardez le tableau extensible en insérant une ligne de tableau dans une feuille de calcul protégée avec le code VBA


Gardez le tableau extensible en insérant une ligne de tableau dans une feuille de calcul protégée avec le code VBA


Comme illustré ci-dessous, une table nommée Table1 dans votre feuille de calcul et la dernière colonne de la table est une colonne de formule. Vous devez maintenant protéger la feuille de calcul pour éviter que la colonne de formule ne change, mais permettre d'agrandir le tableau en insérant une nouvelle ligne et en attribuant de nouvelles données dans les nouvelles cellules. Veuillez faire comme suit.

1. Cliquez Promoteurs > insérer > Bouton (contrôle de formulaire) pour insérer un Contrôle de formulaire bouton dans votre feuille de calcul.

2. Dans le pop-up Attribuer une macro boîte de dialogue, cliquez sur le Nouveau .

3. dans le Microsoft Visual Basic pour applications , veuillez copier et coller le code VBA ci-dessous entre le Sous à ces émotions et de End Sub paragraphes dans le Code fenêtre.

Code VBA: gardez le tableau extensible en insérant une ligne de tableau dans une feuille de calcul protégée

Dim pswStr As String
'Update by ExtendOffice 20181106
    pswStr = "123"
    On Error Resume Next
   Application.ScreenUpdating = False
    ActiveSheet.Unprotect Password:=pswStr
       ActiveSheet.Range("A1").Select
        Range("Table1[[#Headers],[Total]]").Select
        Selection.End(xlDown).Select
        Selection.Offset(1, 0).Select
        ActiveCell.FormulaR1C1 = "new"
        ActiveSheet.Protect Password:=pswStr, DrawingObjects:=False, _
                        Contents:=True, Scenarios:=False, _
                        AllowFormattingCells:=True, AllowFormattingColumns:=True, _
                        AllowFormattingRows:=True, AllowInsertingColumns:=True, _
                        AllowInsertingRows:=True, AllowInsertingHyperlinks:=True, _
                        AllowDeletingColumns:=True, AllowDeletingRows:=True, _
                        AllowSorting:=True, AllowFiltering:=True, _
                        AllowUsingPivotTables:=True
    Selection.ClearContents
    Application.ScreenUpdating = True

Notes:

1). Dans le code, le numéro «123» est le mot de passe que vous utiliserez pour protéger la feuille de calcul.
2). Veuillez modifier le nom de la table et le nom de la colonne contenant la formule que vous protégerez.
3). Et modifiez le décalage dans le code Selection.Offset (1, -4) .Sélectionnez la première colonne cible du tableau. Par exemple, s'il y a six colonnes dans le tableau, modifiez le décalage en (1, -5).

4. appuie sur le autre + Q touches pour fermer la fenêtre Microsoft Visual Basic pour Applications.

5. Sélectionnez les cellules du tableau dans lesquelles vous devez affecter de nouvelles données, à l'exception de la colonne de formule, puis appuyez sur le bouton Ctrl + 1 clés pour ouvrir le Format de cellule boite de dialogue. dans le Format de cellule boîte de dialogue, décochez la case Fermé , puis cliquez sur le OK bouton. Voir la capture d'écran:

6. Protégez maintenant votre feuille de calcul avec le mot de passe que vous avez spécifié dans le code VBA.

À partir de maintenant, après avoir cliqué sur le bouton Contrôle de formulaire dans votre feuille de calcul protégée, le tableau sera extensible en insérant une nouvelle ligne comme illustré ci-dessous.

Notes: vous pouvez modifier le tableau sauf la colonne de formule dans la feuille de calcul protégée.


Articles connexes:


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.
    Olu · 1 years ago
    Hello,

    the code worked initially, but after I duplicated the worksheet, it stayed for after 24 hours then all the code disappeared. And now I can’t access the worksheet.

    it keeps telling me incorrect password. And the code have disappeared. .
  • To post as a guest, your comment is unpublished.
    mrmathews71 · 2 years ago
    Hello, I used the above code and got the following error message:
    "Code execution has been interrupted". When I click on Debug, Line 20 "Selection.ClearContents" is highlighted.

    When I initially entered the code, it worked correctly.

    I changed "Table" to the name of the table and change the column to the name of the column I am using. I also changed the "Selection.Offset (x,-x).Select" to match my needs.


    Any suggestions as to why this is occurring?
  • To post as a guest, your comment is unpublished.
    Mac · 2 years ago
    Try this Vba code for add new line in you table

    Sub Tab_Line_Add()
    Dim pswStr As String
    pswStr = "123"
    On Error Resume Next
    Application.ScreenUpdating = False
    ActiveSheet.Unprotect Password:=pswStr
    ActiveSheet.Range("D8").Select
    'D8 is tabel header
    Range("Table1[[#Headers],[Total]]").Select
    Selection.End(xlDown).Select
    Selection.ListObject.ListRows.Add AlwaysInsert:=False
    ActiveSheet.Protect Password:=pswStr

    End Sub
    .
    • To post as a guest, your comment is unpublished.
      sohael al-husainy · 2 years ago
      using the suggested (Selection.ListObject.ListRows.Add AlwaysInsert:=False) fixed a similar problem for me with the original code, where a new full row (extending down cell contained formulas) would not be added to the table on a much wider table 51 columns. So thanks for sharing and fixing Mac.
    • To post as a guest, your comment is unpublished.
      crystal · 2 years ago
      Hi Mac,
      Thanks for sharing.
  • To post as a guest, your comment is unpublished.
    Brandi · 3 years ago
    So I tried this however it adds a new row at the bottom of the workbook on line 1048576 however my table only has about 800 records. I have no idea why it's doing this!
    • To post as a guest, your comment is unpublished.
      crystal · 3 years ago
      Hi Brindi,
      The code has been updated with the problem solved, please have a try and thank you for your comment.
      • To post as a guest, your comment is unpublished.
        alimughal · 2 years ago
        Hi Crystal, theproblem is same. I created a new table for myself with only 2 rows. Once I click the button the list is expanded till end of table without adding any Rows. It should be adding to Row number 3.
      • To post as a guest, your comment is unpublished.
        Ali · 2 years ago
        Hi Crystal, theproblem is same. I created a new table for myself with only 2 rows. Once I click the button the list is expanded till end of table without adding any Rows. It should be adding to Row number 3.