Sub Erstellen\_Nutzgrad\_Diagramm()
Sheets("Daten").Select
Range("A3").Select
ActiveSheet.Paste
Range("B3:G10").Select
Selection.ClearContents
Range("H3:I10").Select
Selection.Cut
Range("B3").Select
ActiveSheet.Paste
Range("F1").Select
Selection.Copy
Range("C3:C10").Select
ActiveSheet.Paste
Range("G1").Select
Application.CutCopyMode = False
Selection.Copy
Range("D3:smiley:10").Select
ActiveSheet.Paste
Range("A3:G10").Select
Application.CutCopyMode = False
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
Selection.Borders(xlEdgeLeft).LineStyle = xlNone
Selection.Borders(xlEdgeTop).LineStyle = xlNone
Selection.Borders(xlEdgeBottom).LineStyle = xlNone
Selection.Borders(xlEdgeRight).LineStyle = xlNone
Selection.Borders(xlInsideVertical).LineStyle = xlNone
Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
Range("E3:G10").Select
Selection.Interior.ColorIndex = xlNone
Range("A2:smiley:10").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlBottom
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Range("A10").Select
Selection.AutoFill Destination:=Range("A10:A12"), Type:=xlFillDefault
Range("A10:A12").Select
Range("D10").Select
Selection.AutoFill Destination:=Range("D10:smiley:12"), Type:=xlFillDefault
Range("D10:smiley:12").Select
Sheets("Nutzgradbericht").Select
Charts.Add
ActiveChart.ChartType = xlColumnClustered
ActiveChart.SetSourceData Source:=Sheets("Nutzgradbericht").Range("B11")
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(1).XValues = "=Daten!R2C1:R12C1"
ActiveChart.SeriesCollection(1).Values = "=Daten!R2C3:R10C3"
ActiveChart.SeriesCollection(1).Name = "=Daten!R1C3"
ActiveChart.SeriesCollection(2).Values = "=Daten!R2C4:R12C4"
ActiveChart.SeriesCollection(2).Name = "=Daten!R1C4"
ActiveChart.Location Where:=xlLocationAsObject, Name:="Nutzgradbericht"
ActiveChart.HasLegend = True
ActiveChart.Legend.Select
Selection.Position = xlTop
ActiveChart.HasDataTable = False
End Sub
---
Sub Nutzgrad\_Diagramm\_formatieren()
ActiveSheet.Shapes("Chart 29").Select
Selection.Name = "Nutzgraddiagramm"
Range("H27").Select
ActiveSheet.ChartObjects("Nutzgraddiagramm").Activate
ActiveChart.SeriesCollection(2).Select
ActiveChart.SeriesCollection(2).ChartType = xlLine
ActiveChart.SeriesCollection(2).Select
With Selection.Border
.ColorIndex = 4
.Weight = xlThick
.LineStyle = xlContinuous
End With
With Selection
.MarkerBackgroundColorIndex = xlNone
.MarkerForegroundColorIndex = xlNone
.MarkerStyle = xlNone
.Smooth = False
.MarkerSize = 5
.Shadow = False
End With
With Selection.Border
.ColorIndex = 10
.Weight = xlThick
.LineStyle = xlContinuous
End With
With Selection
.MarkerBackgroundColorIndex = xlNone
.MarkerForegroundColorIndex = xlNone
.MarkerStyle = xlNone
.Smooth = False
.MarkerSize = 5
.Shadow = False
End With
ActiveChart.SeriesCollection(1).Select
With Selection.Border
.Weight = xlThin
.LineStyle = xlAutomatic
End With
Selection.Shadow = False
Selection.InvertIfNegative = False
With Selection.Interior
.ColorIndex = 37
.Pattern = xlSolid
End With
ActiveChart.SeriesCollection(1).Points(1).Select
With Selection.Border
.Weight = xlThin
.LineStyle = xlAutomatic
End With
Selection.Shadow = False
Selection.InvertIfNegative = False
With Selection.Interior
.ColorIndex = 41
.Pattern = xlSolid
End With
ActiveChart.ChartArea.Select
ActiveSheet.Shapes("Nutzgraddiagramm").IncrementLeft -48.75
ActiveSheet.Shapes("Nutzgraddiagramm").IncrementTop -6#
ActiveSheet.Shapes("Nutzgraddiagramm").ScaleWidth 1.58, msoFalse, \_
msoScaleFromTopLeft
ActiveSheet.Shapes("Nutzgraddiagramm").ScaleHeight 1.29, msoFalse, \_
msoScaleFromTopLeft
ActiveChart.SeriesCollection(2).Select
With Selection.Border
.ColorIndex = 3
.Weight = xlThick
.LineStyle = xlContinuous
End With
With Selection
.MarkerBackgroundColorIndex = xlNone
.MarkerForegroundColorIndex = xlNone
.MarkerStyle = xlNone
.Smooth = False
.MarkerSize = 5
.Shadow = False
End With
ActiveWindow.Visible = False
End Sub
Gruß
Christian