Discussion :

[thany33]

Bonjour à tous,

J'apprends le VBA en autodidacte avec les vidéos Youtube et vos multiples contributions, alors tout d'abord MERCI !!

J'ai réalisé un programme qui doit me permettre d'atteindre 2 objectifs lors de la modification d'une cellule dans ma feuille :
1- Marquer en couleur + lister les mises à jour d'une plage de cellules définie dans une nouvelle feuille => OK ça marche bien
2- Détecter les doublons dans les colonnes qui se trouvent à droite dans mon tableau (à partir de J8 jusqu'à environ 1000 en bas et loin à droite) => là j'ai 2 problèmes

1er problème j'ai du mal à définir la plage de travail de ce 2ème contrôle. Au début j'ai voulu faire un 2ème programme mais comme j'active 2 fois "Worksheet_Change" sur la même feuille il n'a pas l'air d'accord, alors j'ai tout réuni dans le même programme. Une partie du problème vient peut-être de là.
2ème problème je voudrais faire partir mon contrôle à partir de J8 mais il s'applique aux premières colonnes...

Voici mon code commenté, cf. lignes 48, 56-57 là où je bloque :

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
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
Private Sub Worksheet_Change(ByVal Target As Range)
 Dim PlageCellules As Range, Nbr As Integer, RgIndex As Integer, y As Integer
 RgIndex = Target.Row 'pour récupérer l'index présent sur cette ligne
 y = ActiveCell.Column ' pour identifier la colonne sur laquelle contrôler les doublons
 
' Cacher le travail d'Excel pour ne pas gêner l'utilisateur :
Application.ScreenUpdating = False
 
'Supprimer la protection de la feuille MAJ
Sheets("MAJ").Select
ActiveSheet.Unprotect "nath"
 
' 1 // Sur la partie gauche, suivre les mises à jour en passant les cellules modifiées en surbrillance
'       et en les listant dans la feuille MAJ
 
' La variable PlageCellules contient les cellules dont les modifications seront suivies
Set PlageCellules = Sheets("Gestion").Range("C8:I800")
 
If Not Application.Intersect(Target, PlageCellules) Is Nothing Then
' Si l'une de ces cellules a été modifiée, alors
    Target.Interior.Color = RGB(255, 255, 0) 'passer la cellule concernée en jaune
 
'Récupérer le nombre de ligne existant sur la feuille MAJ pour écrire dessous
Nbr = Sheets("MAJ").Range("G1").Value + 1 'le nombre de lignes est stocké en G1 de la feuille MAJ (écriture non visible)
 
'Alimenter la première ligne vide trouvée avec les différents éléments dans le tableau de la feuille MAJ :
Sheets("MAJ").Range("A" & Nbr) = Now '........................................................Date de modification
Sheets("MAJ").Range("B" & Nbr) = Environ("username") '................................Utilisateur qui a apporté la modification
Sheets("MAJ").Range("C" & Nbr) = Target.Worksheet.Name '...........................Matrice concernée
Sheets("MAJ").Range("D" & Nbr) = Target.Address(False, False, , False, "A1") '.Cellule modifiée
Sheets("MAJ").Range("E" & Nbr) = Sheets("Gestion").Range("I" & RgIndex) '...Index correspondant
Sheets("MAJ").Range("F" & Nbr) = Target.Value '............................................Nouveau contenu de la cellule
 
End If
 
'Rétablir la protection de la feuille MAJ
Sheets("MAJ").Select
   ActiveSheet.Protect "nath", DrawingObjects:=True, Contents:=True, Scenarios:=True _
        , AllowFormattingCells:=True, AllowFormattingColumns:=True, _
        AllowFormattingRows:=True, AllowSorting:=True, AllowFiltering:=True, _
        AllowUsingPivotTables:=True
 
'Se repositionner sur la feuille d'origine (pour que ce soit transparent pour l'utilisateur)
Sheets("Gestion").Select
 
'2 // Signaler et gérer les doublons à la saisie des numéros dans les colonnes de la matrice
 
'S'il existe un doublon dans la colonne active des lignes 8 à 1000 /JE VOUDRAIS EXCLURE LES 9 PREMIERES COLONNES = NE MARCHE PAS
If Application.WorksheetFunction.CountIf(Range(Cells(8, 10), Cells(1000, y)), Target) > 1 Then
'alors signaler le doublon et proposer d'annuler la saisie :
    If MsgBox("Attention... ce numéro a déjà été utilisé." & Chr(10) & "Voulez-vous l'effacer ?", vbYesNo + vbExclamation, "Doublon") = vbYes Then
        If vbYes Then
        Target.Value = ""   ' si OUI annuler la saisie
        Target.Cells.Select ' et se repositionner sur la cellule saisie pour que l'utilisateur entre directement un nouveau numéro
        Else ' Sinon :
        Target.Interior.Color = RGB(248, 187, 208) ' colorer la cellule en rose / NE MARCHE PAS
        Target.Font.Color = vbRed                  ' colorer le texte en rouge / NE MARCHE PAS
        End If
    End If
End If
 
'Remontrer le travail d'Excel
Application.ScreenUpdating = True
 
 
End Sub

A noter que la réponse Oui apporte un résultat satisfaisant : ma cellule est vidée et je reviens dessus.
La réponse Non n'apporte aucun effet (j'ai tenté de mettre une msgbox à la place et elle ne s'affiche pas. La condition ne s'exécute pas du tout)

Auriez vous une solution pour moi ?

Un très grand merci d'avance,

Cdlt,

Nathalie

[thany33]

Bonjour à tous,

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
'2 // Signaler et gérer les doublons à la saisie des numéros dans les colonnes de la matrice

'S'il existe un doublon dans la colonne active des lignes 8 à 1000 /JE VOUDRAIS EXCLURE LES 9 PREMIERES COLONNES = NE MARCHE PAS
If Application.WorksheetFunction.CountIf(Range(Cells(8, 10), Cells(1000, y)), Target) > 1 Then
'alors signaler le doublon et proposer d'annuler la saisie :
    If MsgBox("Attention... ce numéro a déjà été utilisé." & Chr(10) & "Voulez-vous l'effacer ?", vbYesNo + vbExclamation, "Doublon") = vbYes Then
       ' If vbYes Then
        Target.Value = ""   ' si OUI annuler la saisie
        Target.Cells.Select ' et se repositionner sur la cellule saisie pour que l'utilisateur entre directement un nouveau numéro
        Else ' Sinon :
        Target.Interior.Color = RGB(248, 187, 208) ' colorer la cellule en rose / Corrigé ;) 
        Target.Font.Color = vbRed                  ' colorer le texte en rouge /  Corrigé ;) 
        'End If
    End If
End If

En me relisant pour voir si ma question était claire, la réponse m'est apparue pour mon 1er problème ! J'avais 2 fois "Si oui"... en corrigeant comme ci-dessus, ça va beaucoup mieux

Mais je reste à l'écoute de vos suggestions pour parvenir à mieux définir ma zone de travail...

Merci !

[thany33]

Bonjour, me revoilà

Je continue de progresser sur mon code et malgré l'absence de réponse j'ai trouvé mes solutions.

Du coup je peaufine mon programme.

Comme je fais des contrôles sur une plage de données dans un tableau et que les utilisateurs sont susceptibles d'insérer des colonnes ou des lignes, tout risque de se décaler.
J'introduis donc des variables pour que mes contrôles de fassent en fonction de l'emplacement d'une cellule qui contient le mot "Index".

Presque tout fonctionne, saut ma ligne 41 :

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
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
 
Private Sub Worksheet_Change(ByVal Target As Range)
 Dim PlageCellules, Rng As Range
 Dim Nbr, RgIndex, x, y, RngRow, RngCol As Integer
 Dim FeuilleCible As Worksheet
 Dim ColIndex As Range
 
 x = Target.Row 'pour récupérer l'index présent sur cette ligne
 y = Target.Column ' pour identifier la colonne sur laquelle contrôler les doublons
 Set FeuilleCible = ThisWorkbook.Worksheets("Gestion") ' pour définir le nom de la feuille de travail en 1 seule fois (faciliter la copie du code dans d'autres feuilles)
 Set Rng = FeuilleCible.Range("A:FA").Find(What:="Index")
 RngCol = Rng.Column
 RngRow = Rng.Row
 
' Cacher le travail d'Excel pour ne pas gêner l'utilisateur :
Application.ScreenUpdating = False
 
'Supprimer la protection de la feuille MAJ
Sheets("MAJ").Select
ActiveSheet.Unprotect "nath"
 
' 1 // Sur la partie gauche, suivre les mises à jour en passant les cellules modifiées en surbrillance
'      et en les listant dans la feuille MAJ
 
' La variable PlageCellules contient les cellules dont les modifications seront suivies (La cellule qui contient "Index" sert de référence)
Set PlageCellules = FeuilleCible.Range(Cells(RngRow + 1, 1), Cells(RngRow + 10, RngCol))
 
If Not Application.Intersect(Target, PlageCellules) Is Nothing Then
' Si l'une de ces cellules a été modifiée, alors
    Target.Interior.Color = RGB(255, 255, 0) 'passer la cellule concernée en jaune
 
'Récupérer le nombre de ligne existant sur la feuille MAJ pour écrire dessous
Nbr = Sheets("MAJ").Range("G1").Value + 1 'le nombre de lignes est stocké en G1 de la feuille MAJ (écriture non visible)
 
'Alimenter la première ligne vide trouvée avec les différents éléments dans le tableau de la feuille MAJ :
Sheets("MAJ").Range("A" & Nbr) = Now '.........................................Date de modification
Sheets("MAJ").Range("B" & Nbr) = Environ("username") '.........................Utilisateur qui a apporté la modification
Sheets("MAJ").Range("C" & Nbr) = Target.Worksheet.Name '.......................Matrice concernée
Sheets("MAJ").Range("D" & Nbr) = Target.Address(False, False, , False, "A1") '.Cellule modifiée
'Sheets("MAJ").Range("E" & Nbr) = FeuilleCible.Range("I" & x) '.......Index correspondant / cette commande fonctionne mais je veux identifier la colonne "I" via la variable RngCol
Sheets("MAJ").Range("E" & Nbr) = FeuilleCible.Range(Cells(x, RngCol)).Value '.......Index correspondant / Cette commande ne fonctionne pas : "méthode range de l'objet worksheet a échoué"
Sheets("MAJ").Range("F" & Nbr) = Target.Value '................................Nouveau contenu de la cellule
 
End If
 
'Rétablir la protection de la feuille MAJ
Sheets("MAJ").Select
   ActiveSheet.Protect "nath", DrawingObjects:=True, Contents:=True, Scenarios:=True _
        , AllowFormattingCells:=True, AllowFormattingColumns:=True, _
        AllowFormattingRows:=True, AllowSorting:=True, AllowFiltering:=True, _
        AllowUsingPivotTables:=True
 
'Se repositionner sur la feuille d'origine (pour que ce soit transparent pour l'utilisateur)
FeuilleCible.Select
 
'2 // Signaler et gérer les doublons à la saisie des numéros dans les colonnes de la matrice
 
'S'il existe un doublon dans la colonne active des lignes 8 à 1000 / ne se cantonne pas à la colonne active mais à toute la plage = KO
If y > RngCol And x > RngRow Then
    If Application.WorksheetFunction.CountIf(Range(Cells(RngRow + 1, y), Cells(1000, y)), Target) > 1 Then
    'alors signaler le doublon et proposer d'annuler la saisie :
        If MsgBox("Attention... ce numéro a déjà été utilisé." & Chr(10) & "Voulez-vous l'effacer ?", vbYesNo + vbExclamation, "Doublon") = vbYes Then
          Target.Value = ""   ' si OUI annuler la saisie
        Target.Cells.Select ' et se repositionner sur la cellule saisie pour que l'utilisateur entre directement un nouveau numéro
        Else ' Sinon :
        Target.Interior.Color = RGB(248, 187, 208) ' colorer la cellule en rose
        Target.Font.Color = vbRed                  ' colorer le texte en rouge
        Target.Cells.Select
        End If
    End If
End If
 
 
'Remontrer le travail d'Excel
Application.ScreenUpdating = True
 
 
End Sub

Si quelqu'un à une idée ? Si je remplace par la ligne 40 le programme tourne parfaitement.
Mais comme j'ai besoin d'appliquer ce code à de nombreux tableaux (avec la même structure autour de cette fameuse cellule contenant le mot "Index") et que les utilisateurs mettent un nombre variable de colonnes et de lignes avant cette cellule...
Je voudrais que mon code fonctionne toujours quelque soit la feuille sur laquelle je l'applique.

Merci de votre aide.

[Philippe Tulliez]

Bonjour,

Comme je fais des contrôles sur une plage de données dans un tableau et que les utilisateurs sont susceptibles d'insérer des colonnes ou des lignes, tout risque de se décaler.
J'introduis donc des variables pour que mes contrôles de fassent en fonction de l'emplacement d'une cellule qui contient le mot "Index".

Il est possible de connaître la position d'une colonne en utilisant une recherche sur la ligne des titres soit à l'aide de la méthode Find soit en utilisant WorksheetFunction.Match qui est l'équivalent de la fonction EQUIV d'excel (MATCH pour les versions anglaise).
Cependant, si vous utilisez les tableaux structurés (ListObject en VBA) ce serait nettement plus simple à gérer en utilisant directement le nom de la colonne.

Je vous invite donc à lire ces tutoriels si vous ne connaissez les tableaux structurés

• Apprendre à utiliser les tableaux structurés Excel : création, manipulations et avantages(1)
• Excel: Les tables de données en VBA
• et le petit dernier Fonctions en VBA pour gérer les Tableaux Structurés d’Excel

[thany33]

Bonjour, je vous remercie pour votre réponse mais même en ayant parcouru les liens transmis, j'ai beaucoup de mal à imaginer comment cela pourrait s'adapter à mon besoin.

Voici à quoi ressemble un aperçu de tableau (que nous appelons "matrices" car il servent à construire des cas de test) :

Les colonnes à droite d'index doivent contenir des numéros distincts pour appeler les différentes lignes dans leur ordre croissant (mais l'ordre est défini par le numéro utilisé et non la disposition initiale des lignes)
Les colonnes de gauche permettent à l'utilisateur de structurer sa réflexion
J'ajoute la colonne index et derrière chaque index unique j'ai construit une base de donnée dans un autre tableau
Tout cela est ensuite "mouliné" par une autre macro pour reconstituer les scénarios de test.

Mon but ici est donc de sécuriser la construction de ces matrices :
1- en traçant les modifications structurelles réalisées
2- en détectant dès la création les anomalies (doublons dans les numéros qui feront ensuite échouer la reconstitution du scénario)

En fait, j'ai l'impression que je suis presqu'au but et que mon dernier problème doit être tout bête, mais à force d'avoir le nez dessus je ne trouve pas la solution...

[Philippe Tulliez]

Bonjour,

même en ayant parcouru les liens transmis, j'ai beaucoup de mal à imaginer comment cela pourrait s'adapter à mon besoin.

Vous pratiquez la lecture rapide pour avoir pu parcourir trois tutoriels en 30 minutes

L'utilisation des tableaux structurés, aussi bien sans qu'avec le VBA, est tellement simple à utiliser que je ne comprends pas que l'on puisse continuer à utiliser des plages de cellules classiques mais bon chacun fait sa vie comme il se couche.

Si vous voulez chercher l'index d'une colonne, je vous renvoie à ma réponse précédente

Il est possible de connaître la position d'une colonne en utilisant une recherche sur la ligne des titres soit à l'aide de la méthode Find soit en utilisant WorksheetFunction.Match qui est l'équivalent de la fonction EQUIV d'excel (MATCH pour les versions anglaise).

Exemple simple pour trouver l'index de la colonne d'une valeur cherchée dans la ligne des titres à l'aide de Application.WorksheetFunction

Code :

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

1
2
3
4
5
6
7
8
9
10
11
Sub t()
  Const LookupValue As String = "Tva"
  Dim fx As WorksheetFunction
  Dim rngHeader As Range
  Dim Index As Integer
  Set fx = Application.WorksheetFunction
  Set rngHeader = ThisWorkbook.Worksheets("Feuil1").Range("D1:K1")
  Index = fx.Match(LookupValue, Range("D1:K1"), 0)
  MsgBox LookupValue & " se trouve à la colonne " & Index & " de la plage " & rngHeader.Address(external:=True)
  Set fx = Nothing:  Set rngHeader = Nothing
End Sub