Diagrammfarben in Mehrfachdiagrammen
08.06.2005 23:12:34
Jörg
ich habe ein kleines Programm geschrieben, welches mir in einem Mehrfachdiagramm ausgewählte zusammengehörige Datenreihen anzeigt.
Nun möchte ich gerne die Form und Farbe in einer vorgegeben Reihenfolge ablaufen lassen.
Z.B. rot kreis, blau kreis , grün dreieck
Wie kann ich dies hinbekommen?
Gibt es desweiteren eine Möglichkeit den Code etwas kürzer bzw. eleganter zu schreiben?
Viele Grüße
Jörg B.
Sub grafische_Auswertung_ansehen()
Dim datenreihenzaehler As Integer
Dim zaehler_datenreihen As Integer
Dim anzahl_datenreihen As Integer
Dim l As Integer
Dim k As Integer
Application.ScreenUpdating = False
datenreihenzaehler = 1
ActiveWorkbook.Sheets(4).Select
ActiveSheet.ChartObjects("Diagramm 1").Activate
anzahl_datenreihen = ActiveChart.SeriesCollection.Count
If anzahl_datenreihen > 1 Then
For zaehler_datenreihen = 2 To anzahl_datenreihen
ActiveChart.SeriesCollection(2).Delete
Next zaehler_datenreihen
End If
ActiveSheet.ChartObjects("Diagramm 2").Activate
anzahl_datenreihen = ActiveChart.SeriesCollection.Count
If anzahl_datenreihen > 1 Then
For zaehler_datenreihen = 2 To anzahl_datenreihen
ActiveChart.SeriesCollection(2).Delete
Next zaehler_datenreihen
End If
ActiveSheet.ChartObjects("Diagramm 3").Activate
anzahl_datenreihen = ActiveChart.SeriesCollection.Count
If anzahl_datenreihen > 1 Then
For zaehler_datenreihen = 2 To anzahl_datenreihen
ActiveChart.SeriesCollection(2).Delete
Next zaehler_datenreihen
End If
ActiveSheet.ChartObjects("Diagramm 4").Activate
anzahl_datenreihen = ActiveChart.SeriesCollection.Count
If anzahl_datenreihen > 1 Then
For zaehler_datenreihen = 2 To anzahl_datenreihen
ActiveChart.SeriesCollection(2).Delete
Next zaehler_datenreihen
End If
For l = 5 To 100
ActiveWorkbook.Sheets(2).Select
If Cells(l, 1) = "#" Then
datenreihenzaehler = datenreihenzaehler + 1
ActiveWorkbook.Sheets(4).Select
ActiveSheet.ChartObjects("Diagramm 1").Activate
ActiveChart.PlotArea.Select
Application.CutCopyMode = True
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(datenreihenzaehler).XValues = Sheets(2).Cells(l, 8)
ActiveChart.SeriesCollection(datenreihenzaehler).Values = Sheets(2).Cells(l, 7)
ActiveChart.SeriesCollection(datenreihenzaehler).Name = Sheets(2).Cells(l, 4)
ActiveChart.SeriesCollection(datenreihenzaehler).Select
With Selection.Border
.Weight = xlThin
.LineStyle = xlNone
End With
With Selection
.MarkerBackgroundColorIndex = xlAutomatic
.MarkerForegroundColorIndex = xlAutomatic
.MarkerStyle = xlSquare
.Smooth = False
.MarkerSize = 7
.Shadow = False
End With
ActiveSheet.ChartObjects("Diagramm 2").Activate
ActiveChart.PlotArea.Select
Application.CutCopyMode = True
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(datenreihenzaehler).XValues = Sheets(2).Cells(l, 8) 'CO
ActiveChart.SeriesCollection(datenreihenzaehler).Values = Sheets(2).Cells(l, 6) 'NOX
ActiveChart.SeriesCollection(datenreihenzaehler).Name = Sheets(2).Cells(l, 4)
ActiveChart.SeriesCollection(datenreihenzaehler).Select
With Selection.Border
.Weight = xlThin
.LineStyle = xlNone
End With
With Selection
.MarkerBackgroundColorIndex = xlAutomatic
.MarkerForegroundColorIndex = xlAutomatic
.MarkerStyle = xlSquare
.Smooth = False
.MarkerSize = 7
.Shadow = False
End With
ActiveSheet.ChartObjects("Diagramm 3").Activate
ActiveChart.PlotArea.Select
Application.CutCopyMode = True
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(datenreihenzaehler).XValues = Sheets(2).Cells(l, 10) 'PM
ActiveChart.SeriesCollection(datenreihenzaehler).Values = Sheets(2).Cells(l, 7) 'NOX + HC
ActiveChart.SeriesCollection(datenreihenzaehler).Name = Sheets(2).Cells(l, 4)
ActiveChart.SeriesCollection(datenreihenzaehler).Select
With Selection.Border
.Weight = xlThin
.LineStyle = xlNone
End With
With Selection
.MarkerBackgroundColorIndex = xlAutomatic
.MarkerForegroundColorIndex = xlAutomatic
.MarkerStyle = xlSquare
.Smooth = False
.MarkerSize = 7
.Shadow = False
End With
ActiveSheet.ChartObjects("Diagramm 4").Activate
ActiveChart.PlotArea.Select
Application.CutCopyMode = True
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(datenreihenzaehler).XValues = Sheets(2).Cells(l, 10) 'PM
ActiveChart.SeriesCollection(datenreihenzaehler).Values = Sheets(2).Cells(l, 6) 'NOX
ActiveChart.SeriesCollection(datenreihenzaehler).Name = Sheets(2).Cells(l, 4)
ActiveChart.SeriesCollection(datenreihenzaehler).Select
With Selection.Border
.Weight = xlThin
.LineStyle = xlNone
End With
With Selection
.MarkerBackgroundColorIndex = xlAutomatic
.MarkerForegroundColorIndex = xlAutomatic
.MarkerStyle = xlSquare
.Smooth = False
.MarkerSize = 7
.Shadow = False
End With
Else
End If
Next l
datenreihenzaehler = datenreihenzaehler + 1
ActiveWorkbook.Sheets(4).Select
ActiveSheet.ChartObjects("Diagramm 1").Activate
ActiveChart.PlotArea.Select
Application.CutCopyMode = True
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(datenreihenzaehler).XValues = Sheets(4).Range("o10:o12")
ActiveChart.SeriesCollection(datenreihenzaehler).Values = Sheets(4).Range("p10:p12")
ActiveChart.SeriesCollection(datenreihenzaehler).Name = Sheets(4).Range("o9")
ActiveChart.SeriesCollection(datenreihenzaehler).Select
With Selection.Border
.ColorIndex = 46
.Weight = xlMedium
.LineStyle = xlDash
End With
With Selection
.MarkerBackgroundColorIndex = xlNone
.MarkerForegroundColorIndex = xlNone
.MarkerStyle = xlNone
.Smooth = False
.MarkerSize = 7
.Shadow = False
End With
ActiveSheet.ChartObjects("Diagramm 2").Activate
ActiveChart.PlotArea.Select
Application.CutCopyMode = True
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(datenreihenzaehler).XValues = Sheets(4).Range("o10:o12")
ActiveChart.SeriesCollection(datenreihenzaehler).Values = Sheets(4).Range("q10:q12")
ActiveChart.SeriesCollection(datenreihenzaehler).Name = Sheets(4).Range("o9")
ActiveChart.SeriesCollection(datenreihenzaehler).Select
With Selection.Border
.ColorIndex = 46
.Weight = xlMedium
.LineStyle = xlDash
End With
With Selection
.MarkerBackgroundColorIndex = xlNone
.MarkerForegroundColorIndex = xlNone
.MarkerStyle = xlNone
.Smooth = False
.MarkerSize = 7
.Shadow = False
End With
ActiveSheet.ChartObjects("Diagramm 3").Activate
ActiveChart.PlotArea.Select
Application.CutCopyMode = True
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(datenreihenzaehler).XValues = Sheets(4).Range("R10:R12")
ActiveChart.SeriesCollection(datenreihenzaehler).Values = Sheets(4).Range("p10:p12")
ActiveChart.SeriesCollection(datenreihenzaehler).Name = Sheets(4).Range("o9")
ActiveChart.SeriesCollection(datenreihenzaehler).Select
With Selection.Border
.ColorIndex = 46
.Weight = xlMedium
.LineStyle = xlDash
End With
With Selection
.MarkerBackgroundColorIndex = xlNone
.MarkerForegroundColorIndex = xlNone
.MarkerStyle = xlNone
.Smooth = False
.MarkerSize = 7
.Shadow = False
End With
ActiveSheet.ChartObjects("Diagramm 4").Activate
ActiveChart.PlotArea.Select
Application.CutCopyMode = True
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(datenreihenzaehler).XValues = Sheets(4).Range("r10:R12")
ActiveChart.SeriesCollection(datenreihenzaehler).Values = Sheets(4).Range("q10:q12")
ActiveChart.SeriesCollection(datenreihenzaehler).Name = Sheets(4).Range("o9")
ActiveChart.SeriesCollection(datenreihenzaehler).Select
With Selection.Border
.ColorIndex = 46
.Weight = xlMedium
.LineStyle = xlDash
End With
With Selection
.MarkerBackgroundColorIndex = xlNone
.MarkerForegroundColorIndex = xlNone
.MarkerStyle = xlNone
.Smooth = False
.MarkerSize = 7
.Shadow = False
End With
Application.ScreenUpdating = True
ActiveWorkbook.Sheets(4).Range("a1").Select
End Sub