Appel de procédure avec arguments pour RowSource d'une ListBox

Version imprimable

Bonjour à tous,

J'ai récemment appris comment passer des arguments optionnels ou non à une procédure.

Toutefois je n'ai été confronté à l'appel de ce type de procédure que pour le passage des paramètres de graphiques.

Ici je voudrais savoir comment à chaque appel de la procédure pouvoir à la fois transmettre les paramètres du graphique à créer tout en passant la ou les plage(s) devant alimenter l'argument RowSource d'une ListeBox qui irait chercher systématiquement ses données dans un Bilan, Compte de résultats et une série de ratios (Feuille 3). Graphiques et données financières apparaissant tous en Feulle 1.

En annexe je fourni un document .pdf se composant à la fois de la feuille 1 sur laquelle on peut voir que j'ai appelé le graphique Key Financials et de la feuille 3 dans laquelle se trouve les bilan, compte de résultat et les ratios.

Pour enfoncer le clou je précise donc qu'IDEALEMENT j'aimerais accompagner mon graphique d'une fenêtre affichant les données financières dudit graphique. Cela aide mieux à l'analyse selon moi.

D'avance merci de votre temps et patience.

Ricardo.

PS Ci-dessous les deux procédures clés de ma requête. Celle appelant le graphique Key Financial. Ansi que celle, Chart_PL_Analysis, recevant les arguments et dans laquelle devrait apparaître le code pour l'affichage de la ListBox.
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 BTNPLAnalysisRatiosMenuKF_Click() Dim PlageF As Variant Dim Plage1 As Range Dim Plage2 As Range Dim Plage3 As Range Dim Plage4 As Range Dim Plage5 As Range Dim Etiquette1 As Variant Dim Etiquette2 As Variant Dim Etiquette3 As Variant Dim Etiquette4 As Variant Dim Etiquette5 As Variant Dim ChartTitleA As String Dim ChartTypeA As Variant Dim ChartNameA As Variant ChartTypeA = xlColumnClustered ChartTitleA = "Key Financials" ChartNameA = "Key Financials" Set Plage1 = Worksheets(1).Range("I35:K35") Set Plage2 = Worksheets(1).Range("I37:K37") Set Plage3 = Worksheets(1).Range("I40:K40") Set Plage4 = Worksheets(1).Range("I42:K42") Set Plage5 = Worksheets(1).Range("I44:k44") Etiquette1 = Worksheets(1).Range("G35") Etiquette2 = Worksheets(1).Range("G37") Etiquette3 = Worksheets(1).Range("G40") Etiquette4 = Worksheets(1).Range("G42") Etiquette5 = Worksheets(1).Range("G44") Call Chart_PL_Analysis(ChartNameA, ChartTypeA, ChartTitleA, Plage1, Etiquette1, Etiquette2, Etiquette3, Etiquette4, Etiquette5, Plage2, Plage3, Plage4, Plage5) End Sub
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 40 41 42 43 Private Sub Chart_PL_Analysis(CharTNamePLA As Variant, ChartTypePLA As Variant, ChartTitlePLA As String, Plage1 As Range, Optional Etiquette1 As Variant, Optional Etiquette2 As Variant, Optional Etiquette3 As Variant, Optional Etiquette4 As Variant, Optional Etiquette5 As Variant, Optional Plage2 As Variant, Optional Plage3 As Variant, Optional Plage4 As Variant, Optional Plage5 As Variant) Dim Plage As Range Dim Mon_Graphique As Shape Dim Ma_Feuille As Worksheet On Error Resume Next Set Plage = Plage1 If Not IsMissing(Plage2) Then Set Plage = Union(Plage, Plage2) If Not IsMissing(Plage3) Then Set Plage = Union(Plage, Plage3) If Not IsMissing(Plage4) Then Set Plage = Union(Plage, Plage4) If Not IsMissing(Plage5) Then Set Plage = Union(Plage, Plage5) Set Ma_Feuille = ThisWorkbook.Worksheets(1) Set Mon_Graphique = Ma_Feuille.Shapes.AddChart With Mon_Graphique .Top = Range("e12").Top .Left = Range("e12").Left .Height = Range("e12:h28").Height .Width = Range("e12:h28").Width .Name = CharTNamePLA With .Chart .SetSourceData Plage, xlRows .SeriesCollection(1).XValues = "='Balance Sh. and P&L'!$D$7:$F$7" .SeriesCollection(1).Name = Etiquette1 .SeriesCollection(2).Name = Etiquette2 .SeriesCollection(3).Name = Etiquette3 .SeriesCollection(4).Name = Etiquette4 .SeriesCollection(5).Name = Etiquette5 .ChartType = ChartTypePLA .HasTitle = True .ChartTitle.Text = ChartTitlePLA End With End With Set Ma_Feuille = Nothing Set Mon_Graphique = Nothing Set Plage = Nothing End Sub

Si j'ai bien compris, tu ajoute un paramètre ListBox

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
 
Private Sub Chart_PL_Analysis(ListBox As Object, CharTNamePLA As Variant, ChartTypePLA As Variant, ChartTitlePLA As String, Plage1 As Range, Optional Etiquette1 As Variant, Optional Etiquette2 As Variant, Optional Etiquette3 As Variant, Optional Etiquette4 As Variant, Optional Etiquette5 As Variant, Optional Plage2 As Variant, Optional Plage3 As Variant, Optional Plage4 As Variant, Optional Plage5 As Variant)
Dim Plage As Range
Dim Mon_Graphique As Shape
Dim Ma_Feuille As Worksheet
 
On Error Resume Next
 
Set Plage = Plage1
ListBox.AddItem Plage1.Address(0, 0)
If Not IsMissing(Plage2) Then
   Set Plage = Union(Plage, Plage2)
   ListBox.AddItem Plage2.Address(0, 0)
End If
'.....

16/01/2011, 18h12
RicardoBxl

Cher Mercatog,

Je viens avec plaisir de lire ta réponse. Cependant je voudrais m'assurer de bien comprendre.

Avec le code que tu as écris j'aurais bien une fenêtre qui s'affichera reprenant dans le cas présent plusieurs lignes de ratios ?

Concrètement je passe la plage de Nett Working Capital (Fonds de roulement net) et une fenêtre s'affichera avec les données financières de Nett Working Capital ?

J'essaie entre temps sur mon portable ton code.
16/01/2011, 18h24
mercatog
Donc, mes excuses, je n'avais pas bien compris.
Ma proposition était la suivante:
Sur Ta feuille une listbox nommée ListBox1 qui reprend les adresses des plages qui sont entrés en paramètre dans Chart_PL_Analysis.

et l'appel se fera
Code:

1 2 '.... Call Chart_PL_Analysis(Worksheets(1).ListBox1, ChartNameA, ChartTypeA, ChartTitleA, Plage1, Etiquette1, Etiquette2, Etiquette3, Etiquette4, Etiquette5, Plage2, Plage3, Plage4, Plage5)

Code modifié mais je capte pas tout

Voici le code des procédures Key Financial et Chart_PL_Analysis modifiés.

Mais je l'avoue je ne maîtrise pas du tout les ListBox car je ne m'y suis jamais attaqué auparavant.

Comment fais-je pour dire ok la rowsource est là et affiche la ListBox ?

Ci-dessous comme tu le proposes je mets ListBox as Object et plus loin le code mais j'affiche concrètement comment ?

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
40
41
42
43
44
45
46
47
48
49
50
Private Sub Chart_PL_Analysis(Listbox As Object, CharTNamePLA As Variant, ChartTypePLA As Variant, ChartTitlePLA As String, Plage1 As Range, Optional Etiquette1 As Variant, Optional Etiquette2 As Variant, Optional Etiquette3 As Variant, Optional Etiquette4 As Variant, Optional Etiquette5 As Variant, Optional Plage2 As Variant, Optional Plage3 As Variant, Optional Plage4 As Variant, Optional Plage5 As Variant)

Dim Plage As Range
Dim Mon_Graphique As Shape
Dim Ma_Feuille As Worksheet

On Error Resume Next
 
Set Plage = Plage1

Listbox.AddItem Plage1.Address(0, 0)
If Not IsMissing(Plage2) Then
   Set Plage = Union(Plage, Plage2)
   Listbox.AddItem Plage2.Address(0, 0)
End If

If Not IsMissing(Plage2) Then Set Plage = Union(Plage, Plage2)
If Not IsMissing(Plage3) Then Set Plage = Union(Plage, Plage3)
If Not IsMissing(Plage4) Then Set Plage = Union(Plage, Plage4)
If Not IsMissing(Plage5) Then Set Plage = Union(Plage, Plage5)
Set Ma_Feuille = ThisWorkbook.Worksheets(1)
Set Mon_Graphique = Ma_Feuille.Shapes.AddChart

With Mon_Graphique
    .Top = Range("e12").Top
    .Left = Range("e12").Left
    .Height = Range("e12:h28").Height
    .Width = Range("e12:h28").Width
    .Name = CharTNamePLA
    
    With .Chart
        .SetSourceData Plage, xlRows
        .SeriesCollection(1).XValues = "='Balance Sh. and P&L'!$D$7:$F$7"
        .SeriesCollection(1).Name = Etiquette1
        .SeriesCollection(2).Name = Etiquette2
        .SeriesCollection(3).Name = Etiquette3
        .SeriesCollection(4).Name = Etiquette4
        .SeriesCollection(5).Name = Etiquette5
        .ChartType = ChartTypePLA
        .HasTitle = True
        .ChartTitle.Text = ChartTitlePLA
    End With
     
End With

Set Ma_Feuille = Nothing
Set Mon_Graphique = Nothing
Set Plage = Nothing

End Sub

Ici je fourni comme argument la plage1 (exemple) vu que je désire afficher les chiffres provenant de cette plage ?

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
Private Sub BTNPLAnalysisRatiosMenuKF_Click()

Dim Plage1 As Range
Dim Plage2 As Range
Dim Plage3 As Range
Dim Plage4 As Range
Dim Plage5 As Range

Dim Etiquette1 As Variant
Dim Etiquette2 As Variant
Dim Etiquette3 As Variant
Dim Etiquette4 As Variant
Dim Etiquette5 As Variant


Dim ChartTitleA As String
Dim ChartTypeA As Variant
Dim ChartNameA As Variant

ChartTypeA = xlColumnClustered
ChartTitleA = "Key Financials"
ChartNameA = "Key Financials"

Set Plage1 = Worksheets(1).Range("I35:K35")
Set Plage2 = Worksheets(1).Range("I37:K37")
Set Plage3 = Worksheets(1).Range("I40:K40")
Set Plage4 = Worksheets(1).Range("I42:K42")
Set Plage5 = Worksheets(1).Range("I44:k44")

Etiquette1 = Worksheets(1).Range("G35")
Etiquette2 = Worksheets(1).Range("G37")
Etiquette3 = Worksheets(1).Range("G40")
Etiquette4 = Worksheets(1).Range("G42")
Etiquette5 = Worksheets(1).Range("G44")

Call Chart_PL_Analysis(Plage1, ChartNameA, ChartTypeA, ChartTitleA, Plage1, Etiquette1, Etiquette2, Etiquette3, Etiquette4, Etiquette5, Plage2, Plage3, Plage4, Plage5)

End Sub

16/01/2011, 18h32
RicardoBxl

Ah lol tu écrivais déjà quelque chose entre temps. Mais bon je vais faire des tests.

Bon là je suis perdu tu peux faire comme pour les enfants un dessin :oops:
16/01/2011, 18h38
mercatog

Tu insère une listbox ActiveX sur ta feuille, tu la nomme ListBox1

Code:

Call Chart_PL_Analysis(Worksheets(1).ListBox1, ChartNameA, ChartTypeA, ChartTitleA, Plage1, Etiquette1, Etiquette2, Etiquette3, Etiquette4, Etiquette5, Plage2, Plage3, Plage4, Plage5)

permet de remplir ta listbox par les adresses de tes plages

OK Mercatog,

Mais ca ne marche pas. Mais je le reconnais aussi c'est dû à ma méconnaissance.

Bon dans l'ordre:

- J'ai crée un controle ActiveX type ListBox: ListBox1;

- Dans une des procédures j'ai écrit ceci comme code:

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
Private Sub BTNPLAnalysisAssets_click()

Dim Plage1 As Range
Dim Plage2 As Range

Dim Etiquette1 As Variant
Dim Etiquette2 As Variant

Dim ChartTitleA As String
Dim ChartTypeA As Variant
Dim ChartNameA As Variant

ChartTypeA = xlColumnClustered
ChartTitleA = "Assets"
ChartNameA = "Assets"

Etiquette1 = worksheets(3).Range("A8")
Etiquette2 = worksheets(3).Range("A14")

Set Plage1 = worksheets(3).Range("D8:F8")
Set Plage2 = worksheets(3).Range("D14:F14")

Call Chart_PL_Analysis(worksheets(1).ListBox1, ChartNameA, ChartTypeA, ChartTitleA, Plage1, Etiquette1, Etiquette2, , , , Plage2)

End Sub

- Dans la procédure appelée, donc Chart_PL_Analysis, je n'ai rien indiqué pourtant je dois quand-même- cela me semble logique- faire référence au passage de la ListBox, non ?

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
40
41
42
43
Private Sub Chart_PL_Analysis(CharTNamePLA As Variant, ChartTypePLA As Variant, ChartTitlePLA As String, Plage1 As Range, Optional Etiquette1 As Variant, Optional Etiquette2 As Variant, Optional Etiquette3 As Variant, Optional Etiquette4 As Variant, Optional Etiquette5 As Variant, Optional Plage2 As Variant, Optional Plage3 As Variant, Optional Plage4 As Variant, Optional Plage5 As Variant)
 
Dim Plage As Range
Dim Mon_Graphique As Shape
Dim Ma_Feuille As Worksheet
 
On Error Resume Next
 
Set Plage = Plage1
If Not IsMissing(Plage2) Then Set Plage = Union(Plage, Plage2)
If Not IsMissing(Plage3) Then Set Plage = Union(Plage, Plage3)
If Not IsMissing(Plage4) Then Set Plage = Union(Plage, Plage4)
If Not IsMissing(Plage5) Then Set Plage = Union(Plage, Plage5)
Set Ma_Feuille = ThisWorkbook.worksheets(1)
Set Mon_Graphique = Ma_Feuille.Shapes.AddChart
 
With Mon_Graphique
    .Top = Range("e12").Top
    .Left = Range("e12").Left
    .Height = Range("e12:h28").Height
    .Width = Range("e12:h28").Width
    .Name = CharTNamePLA
 
    With .Chart
        .SetSourceData Plage, xlRows
        .SeriesCollection(1).XValues = "='Balance Sh. and P&L'!$D$7:$F$7"
        .SeriesCollection(1).Name = Etiquette1
        .SeriesCollection(2).Name = Etiquette2
        .SeriesCollection(3).Name = Etiquette3
        .SeriesCollection(4).Name = Etiquette4
        .SeriesCollection(5).Name = Etiquette5
        .ChartType = ChartTypePLA
        .HasTitle = True
        .ChartTitle.Text = ChartTitlePLA
    End With
 
End With
 
Set Ma_Feuille = Nothing
Set Mon_Graphique = Nothing
Set Plage = Nothing
 
End Sub

En résumé je suis PAUME. Peux-tu me décrire précisément comment écrire ces deux procédures sachant que j'ai crée la ListBox ?

Je pars demain à l'hôpital pour 1 mois et j'aimerais régler ce problème avant.

En tout que dire si ce n'est un ENORME merci pour ta patience.

Premier code: OK
deuxième code

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
Private Sub Chart_PL_Analysis(ListBox As Object, CharTNamePLA As Variant, ChartTypePLA As Variant, ChartTitlePLA As String, Plage1 As Range, Optional Etiquette1 As Variant, Optional Etiquette2 As Variant, Optional Etiquette3 As Variant, Optional Etiquette4 As Variant, Optional Etiquette5 As Variant, Optional Plage2 As Variant, Optional Plage3 As Variant, Optional Plage4 As Variant, Optional Plage5 As Variant)
Dim Plage As Range
Dim Mon_Graphique As Shape
Dim Ma_Feuille As Worksheet
 
On Error Resume Next
 
Set Plage = Plage1
ListBox.AddItem Plage1.Address(0, 0)
If Not IsMissing(Plage2) Then
   Set Plage = Union(Plage, Plage2)
   ListBox.AddItem Plage2.Address(0, 0)
End If
'idem pour les autres plage
'Suite de ton code

la listbox est remplie des adresses des plages.

EDIT
si tu veux que ta listebox récupère les données de chaque plage, ci joint code complet

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
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
Private Sub RempliList(lstbox As Object, ByRef rng As Variant)
Dim j As Byte
With lstbox
   .AddItem rng.Cells(1, 1).Value
   For j = 1 To 2
      .List(.ListCount - 1, j) = rng(1, j + 1)
   Next j
End With
End Sub
 
Private Sub Chart_PL_Analysis(ListBox As Object, CharTNamePLA As Variant, ChartTypePLA As Variant, ChartTitlePLA As String, Plage1 As Range, Optional Etiquette1 As Variant, Optional Etiquette2 As Variant, Optional Etiquette3 As Variant, Optional Etiquette4 As Variant, Optional Etiquette5 As Variant, Optional Plage2 As Variant, Optional Plage3 As Variant, Optional Plage4 As Variant, Optional Plage5 As Variant)
Dim Plage As Range
Dim Mon_Graphique As Shape
Dim Ma_Feuille As Worksheet
 
On Error Resume Next
ListBox.ColumnCount = 3
ListBox.ColumnWidths = "70;70;70"
ListBox.Clear
 
Set Plage = Plage1
RempliList ListBox, Plage1
'----------------------------------------
If Not IsMissing(Plage2) Then
   Set Plage = Union(Plage, Plage2)
   RempliList ListBox, Plage2
End If
'----------------------------------------
If Not IsMissing(Plage3) Then
   Set Plage = Union(Plage, Plage3)
   RempliList ListBox, Plage3
End If
'----------------------------------------
If Not IsMissing(Plage4) Then
   Set Plage = Union(Plage, Plage4)
   RempliList ListBox, Plage4
End If
'----------------------------------------
If Not IsMissing(Plage5) Then
   Set Plage = Union(Plage, Plage5)
   RempliList ListBox, Plage5
End If
'----------------------------------------
Set Ma_Feuille = ThisWorkbook.Worksheets(1)
Set Mon_Graphique = Ma_Feuille.Shapes.AddChart
 
With Mon_Graphique
   .Top = Range("e12").Top
   .Left = Range("e12").Left
   .Height = Range("e12:h28").Height
   .Width = Range("e12:h28").Width
   .Name = CharTNamePLA
 
   With .Chart
      .SetSourceData Plage, xlRows
      .SeriesCollection(1).XValues = "='" & Ma_Feuille.Name & "'!$D$7:$F$7"
      .SeriesCollection(1).Name = Etiquette1
      .SeriesCollection(2).Name = Etiquette2
      .SeriesCollection(3).Name = Etiquette3
      .SeriesCollection(4).Name = Etiquette4
      .SeriesCollection(5).Name = Etiquette5
      .ChartType = ChartTypePLA
      .HasTitle = True
      .ChartTitle.Text = ChartTitlePLA
   End With
 
End With
 
Set Ma_Feuille = Nothing
Set Mon_Graphique = Nothing
Set Plage = Nothing
End Sub
 
Sub Macro2()
Dim Plage1 As Range, Plage2 As Range, Plage3 As Range, Plage4 As Range, Plage5 As Range
Dim Etiquette1 As String, Etiquette2 As String, Etiquette3 As String, Etiquette4 As String, Etiquette5 As String
Dim ChartTitleA As String, ChartNameA As String
Dim ChartTypeA As Integer
 
ChartTypeA = xlColumnClustered
ChartTitleA = "Key Financials"
ChartNameA = "Key Financials"
 
Set Plage1 = Worksheets(1).Range("I35:K35")
Set Plage2 = Worksheets(1).Range("I37:K37")
Set Plage3 = Worksheets(1).Range("I40:K40")
Set Plage4 = Worksheets(1).Range("I42:K42")
Set Plage5 = Worksheets(1).Range("I44:k44")
 
Etiquette1 = Worksheets(1).Range("G35")
Etiquette2 = Worksheets(1).Range("G37")
Etiquette3 = Worksheets(1).Range("G40")
Etiquette4 = Worksheets(1).Range("G42")
Etiquette5 = Worksheets(1).Range("G44")
 
Call Chart_PL_Analysis(Worksheets(1).ListBox1, ChartNameA, ChartTypeA, ChartTitleA, Plage1, Etiquette1, Etiquette2, Etiquette3, Etiquette4, Etiquette5, Plage2, Plage3, Plage4, Plage5)
 
End Sub

16/01/2011, 22h06
RicardoBxl

Passion ou développeur ?

Coucou Mercatog,

Je vais tester tout çà dans quelques minutes.

Comme dis-moi, tu es développeur ou c'est ta passion de l'informatique ou la débrouillardise qui t'amène à avoir de telles connaissances ?

Perso je suis comptable de formation et j'ai toujours bossé dans le Credit Management et pratique donc VBA par passion d'l'informatique et l'envie d'automatiser des tâches accomplies 1000 fois dans ma carrière.

Je te pose la question car franchement tu m'as beaucoup aidé jusqu'à présent et sur certains points très pointus.

Quoi qu'il en soit je te souhaite une excellente soirée