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
| Sub test_graph_2()
ThisWorkbook.Activate
For Each chart1 In sheets(5).ChartObjects
chart1.Delete
Next
Application.ScreenUpdating = False
Dim ligne As Integer
ligne = 5
Union(Range("C4:N4"), Range(Cells(ligne, 3), Cells(ligne, 14))).Select
While sheets("Taux de service mensuel 2").Range("B" & ligne).Value <> ""
sheets(5).Range("$C$4:$N$4").Select
Set chart1 = sheets(5).Shapes.AddChart
adresse = sheets(5).Range(Cells(ligne, 3), Cells(ligne, 14)).Address
chart1.Chart.SeriesCollection.Add Source:=sheets(5).Range(adresse)
chart1.Chart.ChartType = xlColumnClustered
chart1.Chart.SeriesCollection(1).Select
chart1.Chart.SeriesCollection(1).ChartType = xlLine
chart1.Chart.SeriesCollection(1).Name = "='Taux de service Mensuel 2'!$B$4"
chart1.Chart.SeriesCollection(2).Name = "=""Taux de service"""
chart1.Chart.SeriesCollection(2).XValues = "='Taux de service Mensuel 2'!$C$3:$N$3"
chart1.Chart.SetElement (msoElementChartTitleAboveChart)
chart1.Chart.ChartTitle.Text = Cells(ligne, 2)
Selection.Format.TextFrame2.TextRange.Characters.Text = Cells(ligne, 2)
With Selection.Format.TextFrame2.TextRange.Characters(1, 1).ParagraphFormat
.TextDirection = msoTextDirectionLeftToRight
.Alignment = msoAlignCenter
End With
With Selection.Format.TextFrame2.TextRange.Characters(1, 1).Font
.BaselineOffset = 0
.Bold = msoTrue
.NameComplexScript = "+mn-cs"
.NameFarEast = "+mn-ea"
.Fill.Visible = msoTrue
.Fill.ForeColor.RGB = RGB(0, 0, 0)
.Fill.Transparency = 0
.Fill.Solid
.Size = 18
.Italic = msoFalse
.Kerning = 12
.Name = "+mn-lt"
.UnderlineStyle = msoNoUnderline
.Strike = msoNoStrike
End With
chart1.Left = 3
chart1.Top = 1000 + 225 * ligne
For Each Legraph In chart1.Chart.ChartObjects
Legraph.Select
chart1.Chart.HasAxis(xlValue) = True
chart1.Chart.Axes(xlValue).Select
chart1.Chart.Axes(xlValue).MaximumScale = 1
Next
ligne = ligne + 1
DoEvents
Wend
Application.ScreenUpdating = True
End Sub |
Partager