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
| Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim r2006, r2007, r2008, r2009, r2010, rtotal, rannee As Range
ligne = Target.Row
colonne = Target.Column
If colonne = 2 And ligne > 7 Then
'Permet de prendre la plage des valeurs souhaité
Set r2006 = Worksheets("Communes").Range(Worksheets("Communes").Cells(ligne, 4), Worksheets("Communes").Cells(ligne + 2, 4))
Set r2007 = Worksheets("Communes").Range(Worksheets("Communes").Cells(ligne, 6), Worksheets("Communes").Cells(ligne + 2, 6))
Set r2008 = Worksheets("Communes").Range(Worksheets("Communes").Cells(ligne, 8), Worksheets("Communes").Cells(ligne + 2, 8))
Set r2009 = Worksheets("Communes").Range(Worksheets("Communes").Cells(ligne, 10), Worksheets("Communes").Cells(ligne + 2, 10))
Set r2010 = Worksheets("Communes").Range(Worksheets("Communes").Cells(ligne, 12), Worksheets("Communes").Cells(ligne + 2, 12))
Set rannee = Union(Worksheets("Communes").Cells(7, 4), Worksheets("Communes").Cells(7, 6), Worksheets("Communes").Cells(7, 8), Worksheets("Communes").Cells(7, 10), Worksheets("Communes").Cells(7, 12))
'Regroupe les différentes plages de valeurs
Set rtotal = Union(r2006, r2007, r2008, r2009, r2010)
rtotal.Select
'ajoute le nouveau graph
ThisWorkbook.Charts.Add.Select
ActiveChart.ChartType = xl3DColumnClustered
ActiveChart.SetElement (msoElementChartTitleCenteredOverlay)
ActiveChart.ChartTitle.Text = Cells(ligne, colonne).Value
ActiveChart.SeriesCollection(1).XValues = rannee
ActiveChart.SeriesCollection(1).Name = "=""CA1"""
ActiveChart.SeriesCollection(2).Name = "=""CA2"""
ActiveChart.SeriesCollection(3).Name = "=""CA3"""
ActiveSheet.Name = "Graph"
End If
End Sub |
Partager