Discussion :

[Philippe Tulliez]

Contexte
Pour bénéficier de la création dynamique des lignes et colonnes d'un tableau structuré, il est impossible de protéger la feuille parente de celui-ci.

Dans cette contribution je vous propose une procédure événementielle Worksheet_SelectionChange qui empêche la sélection de toute cellule contenant une formule, évitant ainsi à l'utilisateur d'effacer par mégarde une formule lors de l'encodage.

Pour fonctionner

• un seul tableau dans la feuille parente (ListObject(1))
• le tableau doit commencer en cellule A1
• Créer une cellule nommée IsTest (voir ci-dessous)

Fonctionnalités

• Si aucune ligne n'est présente dans le tableau (DataBodyRange = Nothing), la première ligne est insérée dès que l'on sélectionne une cellule de la ligne vide du tableau ou la ligne de titre.
• En se déplaçant à l'aide de la touche Tab ou Return, une ligne est automatiquement insérée dès que l'on atteint la dernière ligne, dernière colonne autorisée (contenant une constante)
• La sélection d'une cellule non autorisée (contenant une formule) provoque la sélection de la cellule suivante et va à la ligne suivante si la ou les cellules non autorisées se trouve en dernière colonne de la ligne
• Important : La procédure prévoit la possibilité d'effectuer une maintenance.
  Il y a donc lieu de créer une cellule nommée IsTest (A placer par exemple dans une feuille "paramètre" et qui si elle contient la valeur VRAI (pour la version française pou TRUE pour version anglaise) permettra de sélectionner les cellules contenant une formule. Dans le cas contraire (en mode production), il y a lieu qu'elle contienne FAUX (FALSE) ou qu'elle soit vide

Illustration
Dans l'illustration ci-dessous (Exemple du classeur à télécharger), les colonnes A, C, E et F contiennent une formule et il est donc impossible de sélectionner une cellule de l'une d'elles.



Code de la procédure

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
  ' Evite la sélection des cellules contenant une formule dans un Tableau structuré
  '    et insère une ligne lorsque l'on atteint la dernière colonne de la dernière ligne
  ' Le tableau traité doit être seul sur la feuille
  ' Afin de pouvoir effectuer la maintenance
  '    Une cellule nommée "IsTest" doit être crée contenant soit VRAI (vers FR) soit FAUX (ou vide)
  '    Si VRAI la procédure est désactivée
  ' Author : Philippe Tulliez http://www.MagicOffice.be
  '
  Dim oLst As ListObject
  Dim LastRow As Long
  Set oLst = Me.ListObjects(1)
  With oLst
    If .ShowTotals Then LastRow = .TotalsRowRange.Row
    If Target.Row <> LastRow Then
      If Not Application.Intersect(Target, .Range) Is Nothing Then
         If ThisWorkbook.Names("istest").RefersToRange.Value Then Exit Sub
         If .DataBodyRange Is Nothing Then
            .ListRows.Add
            .DataBodyRange.Cells(1, 1).Select
          Else
            If Target.HasFormula Then
               If .DataBodyRange.Columns.Count = Target.Column Then
                   If .DataBodyRange.Rows.Count = Target.Row - 1 Then .ListRows.Add
                   Target.Offset(1, -(.DataBodyRange.Columns.Count - 1)).Select
                Else
                   Target.Offset(ColumnOffset:=1).Select
               End If
            End If
         End If
      End If
    End If
  End With
  Set oLst = Nothing
End Sub

Liens
Je renvoie vers ces tutoriels pour apprendre la manière de gérer les tableaux structurés et comprendre les événements.

• Apprendre à utiliser les tableaux structurés Excel : création, manipulations et avantages(1)
• Les évènements dans la feuille de calcul Excel

Test
Malgré le soin apporté à la programmation de cette procédure et les multiples tests réalisés, il est possible qu'il subsiste une erreur qui m'aurait échappé. N'hésitez pas à m'en faire part.
Vos remarques et réactions sont les bienvenues.

Classeur
Un classeur est disponible en téléchargement avec un petit exemple
Important : Avant d'utiliser le classeur, il y a lieur de faire un Copier/Collage Spécial-Valeur sur la plage C2:C11 de la feuille nommée Stock. Merci à Curt de m'avoir signalé le problème.

[curt]

Bonjour Philippe,

voilà une fonction qui est très intéressante et utile pour éviter aux maladroits de "pourrir" un tableau.

J'ai remarqué un problème lorsqu'on double-clic sur une des cellules "protégées" (C / E / F) - Les valeurs changent

[Philippe Tulliez]

Bonjour Curt,
Merci pour ton appréciation et merci également de m'avoir signaler non pas un bug mais un oubli de ma part.
En effet pour remplir rapidement des valeurs dans des cellules en vue d'effectuer des tests, j'utilise la fonction ALEA.ENTRE.BORNES qui est une fonction volatile et qui donc recalcule de nouvelles valeurs aléatoires à chaque modification de cellules dans n'importe qu'elle feuille de n'importe quel classeur ouvert.

Il y a donc lieu de faire un Copier/Collage Spécial-Valeur sur la plage C2:C11 de la feuille nommée Stock

[zenpbb]

Bonjour Philippe,

Proposition effectivement très intéressante pour la gestion quotidienne d'un tableau qui contient des formules.


Petite suggestion : on pourrait remplacer l'identification du tableau

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

Set oLst = Me.ListObjects(1)

par

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
  Set oLst = Target.ListObject
  If Not oLst Is Nothing Then
      ...

, ce qui permettrait d'éviter ensuite le

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

If Not Application.Intersect(Target, .Range) Is Nothing Then

Et ça pourrait surtout ouvrir la voie à une adaptation assez simple à un tableau pas forcément unique et pas forcément placé en A1 (juste la position relative du tableau sur la feuille à gérer).

[Philippe Tulliez]

Bonjour Zenpbb,
Merci pour ton message et surtout pour ta suggestion
C'est vrai que je n'avais pas pensé à prendre cette voie qui est finalement plus simple.
Je prépare une nouvelle version qui ne se limitera pas à un ListObject débutant en cellule A1 où j'utiliserai Target.ListObject

[Kaytilou]

Bonjour Philippe
Merci de m'avoir aiguillée sur cette page car mon problème était de devoir ajouter une ligne dans une feuille protégée. Je vais donc opter pour cette solution.

Que je comprenne bien, est-il essentiel de devoir commencer le tableau en cellule A1. Dans mon cas, le tableau structuré commence en ligne A6. Puis je donc adapter le code où faut-il absolument commencer en cellule A1

D'autre part, suite à votre dernier échange avec Zenpbb, vous disiez que vous alliez préparer une nouvelle version qui ne se limitera pas à un ListObject débutant en cellule A1 où vous utilisez Target.ListObject

Avez vous pu poster ce nouveau code ? Serait-il possible de le partager car de mon côté, n'étant pas une experte, (j'y vais progressivement mais j'apprend), je suis incapable d'aller jusqu'au bout de ce code.

Il ne me reste plus que cette fonction pour finaliser mon tableau de bord .

Merci pour votre aide et je vous souhaite une très bonne journée

[Philippe Tulliez]

Bonjour,

Nouvelle version
Cette nouvelle version permet de placer le tableau n'importe où sur la feuille et il ne doit plus être le seul

Code de la procédure événementielle Worksheet_SelectionChange
à placer dans le module de la feuille mais peut également être placée dans le module du classeur (Workbook_SheetSelectionChange)

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
  ' Evite la sélection des cellules contenant une formule dans un Tableau structuré
  ' Afin de pouvoir effectuer la maintenance
  '    Une cellule nommée "IsTest" doit être crée contenant soit VRAI (vers FR) soit FAUX (ou vide)
  '    Si VRAI la procédure est désactivée
  ' Philippe Tulliez www.magicoffice.be
  Dim olist As ListObject
  Dim IsMaintenance As Boolean
  Set olist = Target.ListObject
  '
  On Error Resume Next
     IsMaintenance = ThisWorkbook.Names("istest").RefersToRange.Value
  On Error GoTo 0
  '
  If Not IsMaintenance And Not olist Is Nothing Then
     ' Vérifie si une seule cellule a été sélectionnée
     If Target.Count = 1 Then NoSelectFormulaRange Else ActiveCell.Select
  End If
  Set olist = Nothing
End Sub

Code de la procédure NoSelectFormulaRange
à placer dans un Module Standard

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
Function NoSelectFormulaRange()
  ' Evite la sélection des cellules contenant une formule dans un Tableau structuré
  '    et insère une ligne lorsque l'on atteint la dernière colonne de la dernière ligne
  ' Author : Philippe Tulliez http://www.MagicOffice.be
  '
  ' Déclaration
  Dim oRng As Range
  Dim oLst As ListObject
  Dim StartRow As Long, StartColumn As Integer
  Dim LastRow As Long, LastColumn As Integer
  Dim RowPos As Long, ColPos As Integer
  Dim Flag As Boolean
  ' Assignation
  Set oLst = Selection.ListObject
  Set oRng = Selection
  ' Start
  With oLst
    StartRow = .Range.Row: StartColumn = .Range.Column
    LastRow = .Range.Rows.Count
    LastColumn = .Range.Columns.Count
  End With
  RowPos = oRng.Row - StartRow
  ColPos = oRng.Column - StartColumn + 1
  With oLst
    If .ShowTotals Then LastRow = .TotalsRowRange.Row
    With .DataBodyRange
      Select Case True
        Case oLst.DataBodyRange Is Nothing: Flag = True
        Case .Columns.Count = ColPos And .Rows.Count = RowPos And oRng.Cells(1, 1).HasFormula
             Flag = True: RowPos = RowPos + 1
        Case .Rows.Count = RowPos And .Columns.Count < ColPos
             RowPos = RowPos + 1: Flag = True
        Case .Columns.Count = ColPos And oRng.Cells(1, 1).HasFormula
             ColPos = 1: RowPos = RowPos + 1
        Case .Columns.Count < ColPos: ColPos = 1: RowPos = RowPos + 1
        Case oRng.Cells(1, 1).HasFormula: ColPos = ColPos + 1
      End Select
    End With
    ' Insertion d'une ligne
    If Flag Then .ListRows.Add: ColPos = 1: Set oRng = Selection
    ' Sélection de la cellule suivante sauf sur sélection multiple de cellules avec constantes
    If oRng.Count = 1 Or oRng.HasFormula = True Then
       With .DataBodyRange: .Cells(RowPos, ColPos).Select: End With
    End If
  End With
  ' End of Process
  Set oRng = Nothing: Set oLst = Nothing
End Function

[EDIT]
J'ai ajouté un contrôle dans la procédure événementielle car il y a une faille si on sélectionne une ligne ou colonne complète. Si l'on sélectionne plus d'une cellule, c'est la cellule active qui est sélectionnée

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
  If Not IsMaintenance And Not olist Is Nothing Then
     ' Vérifie si une seule cellule a été sélectionnée
     If Target.Count = 1 Then NoSelectFormulaRange Else ActiveCell.Select
  End If

La correction a été intégrée

[Kaytilou]

Bonsoir Philippe
Je vous remercie sincèrement. Je vous suis très reconnaissante pour le temps que vous avez pris pour m'aider à réaliser mon projet. Sans cette aide précieuse, il aurait été difficile d'y arriver tout seule. Je vous souhaite une agréable fin de week-end
Kaytilou

[Philippe Tulliez]

Bonjour,
J'ai revu et allégé la procédure qui empêche de sélectionner les cellules contenant des formules mais également celles qui font partie de la ligne des titres.
J'ai publié le code de la procédure dans un nouveau billet sur mon blog avec le même tritre Excel - VBA - Empêcher la sélection d'une cellule d'un tableau structuré qui contient une formule