Code VBA pour interdire d'insérer ou de supprimer des lignes dans une plage de données

Version imprimable

09/04/2023, 07h31
Sergio33600

Code VBA pour interdire d'insérer ou de supprimer des lignes dans une plage de données

Bonjour le forum,

Dans une feuille Excel, dans une plage de données (lignes 18 à 82) je souhaiterai avec un code, interdire qu'on insère ou qu'on supprime des lignes.

Merci pour votre aide.
09/04/2023, 11h14
Franc

Salut, pourquoi un code alors que tu peux simplement verrouiller les cellules ?
09/04/2023, 11h24
Sergio33600

Bonjour Franc,

Merci de t'intéresser à mon problème. En fait, ce que je voudrai c'est qu'on ne puisse pas insérer ou supprimer de lignes jusqu'à la ligne 82, mais qu'on puisse éventuellement le faire après la ligne 82.
09/04/2023, 13h10
Franc

Tu veux empêcher l'insertion ou suppression de lignes tout en continuant à encoder des valeurs dans les cellules de cette zone (lignes 18 à 82), c'est bien ça ?
09/04/2023, 13h47
Sergio33600

Franc,
Oui, c'est bien ça.
Comme cette feuille Excel, est utilisée par plusieurs personnes et que certains suppriment ou insèrent des lignes alors qu'ils ne devraient pas le faire, ce que je voudrais c'est que les utilisateurs ne puissent pas supprimer ou insérer des lignes jusqu'à la ligne 82, mais qu'ils puissent quand même modifier le contenu des cellules.
Encore merci pour ton aide

Voilà comment je résous en partie ton problème.
D'abord j'empêche l'utilisateur de sélectionner une ligne complète pour éviter de la supprimer ou insérer une nouvelle ligne.
Pour ce faire, si on fait un clic gauche sur un numéro de ligne pour la sélectionner, la sélection est renvoyée vers la cellule A17 (tu peux changer).
Si on sélectionne une cellule ou plage de cellules dans la zone de lignes A18 à A82, dans le clic droit j'ai désactivé le menu "Insérer" et "Supprimer".
Il reste un point faible que je ne sais pas résoudre c'est empêcher les choix "Insérer" et "Supprimer" via le clic droit sur le numéro de ligne, ça doit être faisable via les API windows mais je n'y connais rien.

Code:

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
Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)
    ' Remplacez 9 et 10 par les index des éléments de menu "Insérer" et "Supprimer" fournis par la sub ShowContextMenuControlNamesAndIndexes()
    Const InsertMenuItemIndex As Integer = 9
    Const DeleteMenuItemIndex As Integer = 10
 
    If (Target.Row >= 18 And Target.Row <= 82) Then
        ' Désactive les éléments de menu "Insérer" et "Supprimer" du menu contextuel
        CommandBars("Cell").Controls(InsertMenuItemIndex).Enabled = False
        CommandBars("Cell").Controls(DeleteMenuItemIndex).Enabled = False
    Else
        ' Réactive les éléments de menu "Insérer" et "Supprimer" du menu contextuel
        CommandBars("Cell").Controls(InsertMenuItemIndex).Enabled = True
        CommandBars("Cell").Controls(DeleteMenuItemIndex).Enabled = True
    End If
End Sub
 
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    ' Remplacez 9 et 10 par les index des éléments de menu "Insérer" et "Supprimer" fournis par la sub ShowContextMenuControlNamesAndIndexes()
    Const InsertMenuItemIndex As Integer = 9
    Const DeleteMenuItemIndex As Integer = 10
 
    If (TypeOf Selection Is Range) Then
        If (Selection.Columns.Count = Columns.Count) And (Selection.Row >= 18 And Selection.Row <= 82) And (Selection.Rows.Count = 1) Then
            ' Annule la sélection d'une ligne dans la plage des lignes 18 à 82
            ' en sélectionnant une autre cellule en dehors de cette plage
            Range("A17").Select
        End If
    End If
 
    If (Target.Row >= 18 And Target.Row <= 82) Then
        ' Désactive les éléments de menu "Insérer" et "Supprimer" du menu contextuel
        CommandBars("Cell").Controls(InsertMenuItemIndex).Enabled = False
        CommandBars("Cell").Controls(DeleteMenuItemIndex).Enabled = False
    Else
        ' Réactive les éléments de menu "Insérer" et "Supprimer" du menu contextuel
        CommandBars("Cell").Controls(InsertMenuItemIndex).Enabled = True
        CommandBars("Cell").Controls(DeleteMenuItemIndex).Enabled = True
    End If
End Sub

Lance la sub ci-dessous pour trouver le numéro d'index des éléments "Insérer" et "Supprimer" et éventuellement les changer dans les sub ci-dessus.
Ces numéros varient en fonction de la version Excel.

Code:

1
2
3
4
5
6
Sub ShowContextMenuControlNamesAndIndexes()
    Dim ctrl As CommandBarControl
    For Each ctrl In CommandBars("Cell").Controls
        Debug.Print ctrl.Caption & " - " & ctrl.Index
    Next ctrl
End Sub

09/04/2023, 19h46
Sergio33600

Franc,

Merci beaucoup pour ton aide.
Je ne vois pas trop comment fonctionne la sub pour trouver les numéros d'index. Peux-tu m'éclairer d'avantage ?

Merci
09/04/2023, 20h25
Franc

La sub parcourt les éléments de menu et affiche le nom et numéro d'index. Tu dois ouvrir la fenêtre d"exécution (ctrl + G) avant de lancer la macro.
Tu verras alors les différents éléments du menu avec un numéro d'index. Tu notes le numéro à côté de "Insérer" et "Supprimer" et tu les reportes dans les autres sub à l'endroit où je l'indique.
Chez moi c'est 9 et 10 mais toi c'est peut-être différent.
10/04/2023, 18h27
Sergio33600

Bonjour Franc,

Effectivement, pour moi ce n'était pas les mêmes valeurs (5 et 6).
Encore merci pour ton aide.