Exécution macro lors d'un changement de valeur dans une cellule

**yoyo3d** · 26/01/2015, 13h44

Bonjour à tous,
j'ai une feuille ("interface") dans laquelle j'ai 2 listes déroulantes.
quand je choisi un élément dans la première, en ("A1"), cela me remplis la deuxième liste en ("A2") avec d'autre valeurs.
pour cela, j'utilise le code suivant dans ma feuille ("Interface"):

Code :

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

1
2
3
4
5
 
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$1" Then nb_ligne_article
End Sub

le problème, je souhaite également mettre à jour une troisième liste déroulante quand je choisie un élément dans la deuxième...

je pensais pouvoir rajouter

Code :

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

If Target.Address = "$A$2" Then choix_2

choix_2 étant le code spécifique au traitement de la valeur choisie dans le 2eme menu déroulant....

j’espère avoir été claire...., j'ai bien trouvé des infos sur l’exécution d'une macro lors du changement de valeur dans une cellule, mais je n'arrive pas à combiner les codes liés au changement dans plusieurs cellules sur ma même feuille
j'ai essayé de rajoutér une private sub spécifique mais le code n'est jamais utilisé.

**vinc_bilb** · 26/01/2015, 14h28

Bonjour,

Et bien, je ne comprends pas ce qui ne marche pas dans votre code, (si ce n'est peut-être le test sur les adresses), ça devrait faire ce que vous demandez, même si il risque de ralentir pas mal votre exécution .....
A essayer peut-être (non testé)

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
Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
 
   Debug.Print  Target.Address
 
 
    If Not(Application.Intersect(Target, Sh.Range("A1:A2") Is Nothing) Then
 
      If Target = Sh.Range("A1") Then  
          Call nb_ligne_articl
      Else: 
      If Target = Sh.Range("A2") Then  choix_2
 
   End If
(....)
 
 
   End If
 
End Sub

**Menhir** · 26/01/2015, 14h31

Je ne comprends pas vraiment ton problème.

As-tu essayé tout simplement :

Code :

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

1
2
3
4
5
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$1" Then nb_ligne_article
If Target.Address = "$A$2" Then choix_2
End Sub

Quoi que... avec un select case, ça serait plus propre.

**yoyo3d** · 26/01/2015, 14h59

tout d'abord, merci à vous deux.

j'ai d'abord essayé

Code :

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

1
2
3
4
5
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$1" Then nb_ligne_article
If Target.Address = "$A$2" Then choix_2
End Sub

je n'avais pas de message d'erreur, mais cela ne faisait rien, le code rattaché au choix du menu 2 n'était tout simplement pas exécuté.

quand au test proposé par Vinc_bilb, je ne comprend pas du tout comment cela fonctionne, pourquoi chercher l’intersection de 2 ranges.
il s'agit simplement de 2 cellules contenant chacune un menu déroulant . le premier me filtrant des valeurs qui à leur tour me permettent de sélectionner d'autres valeurs....

**vinc_bilb** · 26/01/2015, 15h24

quand au test proposé par Vinc_bilb, je ne comprend pas du tout comment cela fonctionne, pourquoi chercher l’intersection de 2 ranges.

=> ça évite de passer par les tests si la plage modifiée n'est pas en A1 ou A2 (optimisation de la vitesse), et pour le reste, il devrait faire ce que vous voulez

**Menhir** · 26/01/2015, 15h30

Essaye :

Code :

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

1
2
3
Private Sub Worksheet_Change(ByVal Target As Range)
MsgBox(Target.Adress();vbOKOnly)
End Sub

Ca permettre de savoir si le problème vient de la fonction ou des tests et en plus ça t'affichera le contenu de l'adresse pour vérifier que tu mets les bonnes conditions dans les tests.

**yoyo3d** · 26/01/2015, 17h04

Désolé,
le code

Code :

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

1
2
3
Private Sub Worksheet_Change(ByVal Target As Range)
MsgBox(Target.Adress();vbOKOnly)
End Sub

me donne une erreur de syntaxe.

Quand au test en A1 et A2, il ne doit pas y avoir d'autres cellules à modifier...

**Menhir** · 26/01/2015, 17h52

Envoyé par yoyo3d

me donne une erreur de syntaxe.

Ca serait trop demander que d'avoir la ligne et le message d'erreur ?...

Essaye

Code :

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

1
2
3
Private Sub Worksheet_Change(ByVal Target As Range)
Debug.Print MsgBox(Target.Adress(), vbOKOnly)
End Sub

**yoyo3d** · 26/01/2015, 19h11

oups,

c'est la ligne : MsgBox(Target.Adress();vbOKOnly) qui se met en rouge lors de la saisie et lorsque je "force" l'exécution de la macro, VS me dit erreur de syntaxe en surlignant en jaune cette ligne.
j'essayerais demain matin au boulot avec debug.

merci à vous.

**SfJ5Rpw8** · 26/01/2015, 19h23

bonsoir,

le caractère séparateur de paramètre en VB c'est virgule (,) et pas point-virgule (

modifie l'appel à MsgBox

**casefayere** · 27/01/2015, 00h16

Bonsoir,

D'accord avec Bbil, bien sur et sauf erreur de ma part je ne vois pas l'intérêt d'ajouter "vbOKOnly", j'aurai écris

Code :

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

MsgBox Target.Address()

attention "Address" pas "Adress"

**yoyo3d** · 27/01/2015, 09h21

Bonjour à tous, et merci de vos réponses.

Bon, après vérification de la syntaxe "MsgBox", effectivement, cela m'affiche bien la cellule qui change.(ça m'apprendra à faire des copier coller de code sans vérifier...

)

le fait est que mon code n'atteint jamais la partie ou je traite le choix de ma deuxième liste déroulante.
c'est forcement le déroulement du code qui pose problème. je suis un éternel débutant autodidacte et je n'ai pas la méthode pour définir ou et quand je dois placer telle procédure donc ça devient difficile...

je joint deux bout de code "raccourcis à l'essentiel"

dans mon module j'ai les 2 codes suivants
le premier me tri mes article et me permet de récupérer les listes de famille "unique"

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
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
Function nb_ligne_article()
'compter le nb de ligne feuille articles
nb_ligne_article = Sheets("articles").Range("A1").End(xlDown).Row
Sheets("Interface").Range("F2").Value = nb_ligne_article
 
'trier la liste des articles
Sheets("articles").Activate
'blablabla
 
'- - - - - - - - - - - - - - - - - - - -
'isoler la liste des catégories unique
'- - - - - - - - - - - - - - - - - - - -
 
'nettoyerla liste des catégories
Sheets("BdD").Range("A:A").Clear
 
'mettre à jour la liste des catégories
With Sheets("articles")
Dim cpt_categorie As Integer
cpt_categorie = 1
 
For i = 2 To nb_ligne_article
 
    Sheets("BdD").Range("A" & cpt_categorie).Value = Range("B" & i).Value
    With Sheets("articles")
        If Range("B" & i).Value = Range("B" & i + 1).Value Then
            cpt_categorie = cpt_categorie
        Else
            cpt_categorie = cpt_categorie + 1
        End If
    End With
Next i
 
End With
 
'creer la liste de catégorie pour alimenter la cellule /menu déroulant
Sheets("BdD").Activate
Range("A1:A" & cpt_categorie).Select
ActiveWorkbook.Names.Add Name:="Categorie", RefersTo:=Selection
 
With Sheets("Interface").Select
    Range("B15:D15").Select
    With Selection.Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:="=Categorie"
' blalblabla
    End With
End With
 
'récupération de la valeur de Interface!B15
Dim choix_categorie As String
choix_categorie = Range("B15").Value
Range("G2").Value = choix_categorie
 
'- - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
'isoler les familles possibles en fonction du choix catégorie
'- - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
 
'definir le nombre d'élémenst dans la catégorie choisie
 
Dim famille As String
Dim L1_famille As Integer
Dim Lx_famille As Integer
Dim nb_cat_choix As Integer
 
With Sheets("Articles").Activate
    'trouver la premiere ligne dans la catégorie choisie
    L1_famille = Range("B:B").Find(choix_categorie, lookat:=xlWhole).Row
    'compter le nb de famille dans la catégorie choisie
    nb_cat_choix = Application.WorksheetFunction.CountIf( _
    Range("B:B"), choix_categorie)
    'trouver la derniere ligne dans la catégorie choisie
    Lx_famille = (L1_famille + nb_cat_choix) - 1
 
'créer la liste des familles unique en fonction du choix categorie
Sheets("BdD").Range("B:B").Clear
Dim cpt_famille As Integer
cpt_famille = 1
 
For i = L1_famille To Lx_famille
 
    Sheets("BdD").Range("B" & cpt_famille).Value = Range("D" & i).Value
    With Sheets("articles")
        If Range("D" & i).Value = Range("D" & i + 1).Value Then
            cpt_famille = cpt_famille
        Else
            cpt_famille = cpt_famille + 1
        End If
    End With
Next i
 
'creer la liste de famille pour alimenter la cellule /menu déroulant
Sheets("BdD").Activate
Range("B1:B" & (cpt_famille - 1)).Select
ActiveWorkbook.Names.Add Name:="Famille", RefersTo:=Selection
 
    With Sheets("Interface").Select
        Range("B17").Clear
        Range("B17").Select
        With Selection.Validation
            .Delete
            .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
            xlBetween, Formula1:="=Famille"
  'blablabla
        End With
    End With
 
End With
 
End Function

celui la me permet de faire la même chose pour les catégories d'article en fonction du choix précédent

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
Function choix_type()
 
'- - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
'isoler les type possibles en focntion du choix famille
'- - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
 
Dim typefamille As String
Dim L1_type As Integer
Dim Lx_type As Integer
Dim nb_famille_choix As Integer
 
With Sheets("Articles").Activate
 
'récupération de la valeur de Interface!B15
Dim choix_famille As String
choix_famille = Range("B17").Value
'Range("G3").Value = choix_famille
 
 
    'trouver la premiere ligne dans la famille choisie
    L1_type = Range("D:D").Find(choix_famille, lookat:=xlWhole).Row
    'compter le nb de famille dans la catégorie choisie
    nb_famille_choix = Application.WorksheetFunction.CountIf( _
    Range("D:D"), choix_famille)
    'trouver la derniere ligne dans la catégorie choisie
    Lx_type = (L1_type + nb_famille_choix) - 1
 
End With
 
End Function

et celui la, situé directement dans le code de ma feuille "Interface"
devait me permettre de detecter le changement effectué en B15 et B17 et d'appeler la fonction adéquate

Code :

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

1
2
3
4
5
6
7
Option Explicit
 
Private Sub Worksheet_Change(ByVal Target As Range)
'If Target.Address = "$B$15" Then nb_ligne_article
If Target.Address = "$B$17" Then choix_type
MsgBox Target.Address()
End Sub

j’espère ne pas avoir mis trop de code...

**casefayere** · 27/01/2015, 14h38

je pense que le problème (mais ne suis pas sur !!) vient que les procédures que tu appelles sont des fonctions (et non des Sub) et de plus sans argument

mais je peux me tromper

**yoyo3d** · 27/01/2015, 14h55

C'est tout à fait possible, comme je le dit plus haut, je n'ai jamais vraiment assimilé quand utiliser une fonction ou un subroutine...

**casefayere** · 27/01/2015, 14h59

comme je ne peux plonger dans la problématique, essayes en changeant tes "function" par des "Sub"

J'ai oublier de préciser, attention aux noms des procédures comparées aux noms des variables, à adapter

**Menhir** · 27/01/2015, 15h50

Envoyé par yoyo3d

C'est tout à fait possible, comme je le dit plus haut, je n'ai jamais vraiment assimilé quand utiliser une fonction ou un subroutine...

Une fonction est une portion de code à laquelle on envoie (ou pas) des paramètres et qui retourne une valeur. Cette valeur n'est pas forcément numérique : ça peut être du texte ou une valeur logique. Une fonction a un type (As) comme une variable.
En gros, une fonction fait un calcul, un peu comme une fonction Excel.

Pour tout le reste, on utilise Sub, en particulier ce qui réalise des actions comme modifier le contenu d'une cellule, ce que réalise tes fonctions.