Discussion :

[arnaud63]

Bonjour je débute dans les macro et je souhaiterais comprendre comment faire la chose suivante.
Dans un graphique lié à un TCD j'ai X courbes je voudrais sélectonner le premier point le chaque courbe. Ma macro est faite mais elle est restrictive à 5 courbes.
Comment pourrais je la simplifier et ensuite la rendre évolutive selon mon nombre de courbe.
Je vous envoi si vous avez 5 minutes.

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
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
Application.ScreenUpdating = False
    ActiveSheet.PivotTables("Tableau croisé dynamique1").PivotCache.Refresh
    ActiveSheet.ChartObjects("Graphique 2").Activate
    ActiveChart.SeriesCollection(1).Select
    ActiveChart.SeriesCollection(1).Points(1).Select
    ActiveChart.SeriesCollection(1).Points(1).ApplyDataLabels AutoText:=True, _
        LegendKey:=False, ShowSeriesName:=True, ShowCategoryName:=False, _
        ShowValue:=False, ShowPercentage:=False, ShowBubbleSize:=False
    ActiveChart.SeriesCollection(1).DataLabels.Select
    Selection.AutoScaleFont = True
    With Selection.Font
        .Name = "Arial"
        .FontStyle = "Normal"
        .Size = 9
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .ColorIndex = xlAutomatic
        .Background = xlAutomatic
    End With
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter
        .Position = xlLabelPositionLeft
        .Orientation = xlHorizontal
    End With
    '2
 
    ActiveChart.SeriesCollection(2).Select
    ActiveChart.SeriesCollection(2).Points(1).Select
    ActiveChart.SeriesCollection(2).Points(1).ApplyDataLabels AutoText:=True, _
        LegendKey:=False, ShowSeriesName:=True, ShowCategoryName:=False, _
        ShowValue:=False, ShowPercentage:=False, ShowBubbleSize:=False
    ActiveChart.SeriesCollection(2).DataLabels.Select
    Selection.AutoScaleFont = True
    With Selection.Font
        .Name = "Arial"
        .FontStyle = "Normal"
        .Size = 9
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .ColorIndex = xlAutomatic
        .Background = xlAutomatic
    End With
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter
        .Position = xlLabelPositionLeft
        .Orientation = xlHorizontal
        End With
        '3
 
    ActiveChart.SeriesCollection(3).Select
    ActiveChart.SeriesCollection(3).Points(1).Select
    ActiveChart.SeriesCollection(3).Points(1).ApplyDataLabels AutoText:=True, _
        LegendKey:=False, ShowSeriesName:=True, ShowCategoryName:=False, _
        ShowValue:=False, ShowPercentage:=False, ShowBubbleSize:=False
    ActiveChart.SeriesCollection(3).DataLabels.Select
    Selection.AutoScaleFont = True
    With Selection.Font
        .Name = "Arial"
        .FontStyle = "Normal"
        .Size = 9
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .ColorIndex = xlAutomatic
        .Background = xlAutomatic
    End With
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter
        .Position = xlLabelPositionLeft
        .Orientation = xlHorizontal
    End With
'4
 
    ActiveChart.SeriesCollection(4).Select
    ActiveChart.SeriesCollection(4).Points(1).Select
    ActiveChart.SeriesCollection(4).Points(1).ApplyDataLabels AutoText:=True, _
        LegendKey:=False, ShowSeriesName:=True, ShowCategoryName:=False, _
        ShowValue:=False, ShowPercentage:=False, ShowBubbleSize:=False
    ActiveChart.SeriesCollection(4).DataLabels.Select
    Selection.AutoScaleFont = True
    With Selection.Font
        .Name = "Arial"
        .FontStyle = "Normal"
        .Size = 9
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .ColorIndex = xlAutomatic
        .Background = xlAutomatic
    End With
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter
        .Position = xlLabelPositionLeft
        .Orientation = xlHorizontal
    End With
    '5
    ActiveSheet.ChartObjects("Graphique 2").Activate
    ActiveChart.SeriesCollection(5).Select
    ActiveChart.SeriesCollection(5).Points(1).Select
    ActiveChart.SeriesCollection(5).Points(1).ApplyDataLabels AutoText:=True, _
        LegendKey:=False, ShowSeriesName:=True, ShowCategoryName:=False, _
        ShowValue:=False, ShowPercentage:=False, ShowBubbleSize:=False
    ActiveChart.SeriesCollection(5).DataLabels.Select
    Selection.AutoScaleFont = True
    With Selection.Font
        .Name = "Arial"
        .FontStyle = "Normal"
        .Size = 9
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .ColorIndex = xlAutomatic
        .Background = xlAutomatic
    End With
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter
        .Position = xlLabelPositionLeft
        .Orientation = xlHorizontal
        End With
        Application.ScreenUpdating = True
End Sub

Merci d'avance

[helas]

Créer une procédure FormatageGraphe par exemple

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
Sub FormatageGraphe(graphe as string, ligne as long)
 ActiveSheet.ChartObjects(graphe).Activate
 ActiveChart.SeriesCollection(ligne).Select
 ActiveChart.SeriesCollection(ligne).Points(1).Select
 ActiveChart.SeriesCollection(ligne).Points(1).ApplyDataLabels AutoText:=True, _
 LegendKey:=False, ShowSeriesName:=True, ShowCategoryName:=False, _
 ShowValue:=False, ShowPercentage:=False, ShowBubbleSize:=False
 ActiveChart.SeriesCollection(ligne).DataLabels.Select
 Selection.AutoScaleFont = True 
 With Selection.Font
  .Name = "Arial"
  .FontStyle = "Normal"
  .Size = 9
  .Strikethrough = False
  .Superscript = False
  .Subscript = False
  .OutlineFont = False
  .Shadow = False
  .Underline = xlUnderlineStyleNone
  .ColorIndex = xlAutomatic
  .Background = xlAutomatic
 End With
 With Selection
 .HorizontalAlignment = xlCenter
 .VerticalAlignment = xlCenter
 .Position = xlLabelPositionLeft
 .Orientation = xlHorizontal
 End With
End Sub

qui sera utiliser de cette façon

Code :

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

1
2
3
4
 
 For i = 1 To 5
  Call FormatageGraphe("Graphique 2", i )
 Next i

Ca parait même plus clair et surtout moins long

[arnaud63]

Je suis désolé je nage avec cette solution, je ne sais pas trop comment l'installer.