AW: If (Zelle A2) <> dann
29.06.2007 15:52:00
Boris
Oh...sorry...
Fällt mir manchmal ein wenig schwer, solche Probleme so zu schildern, daß andere direkt verstehen was ich meine.
Aber es klappt immer noch nicht...wie schon gesagt...VBA in Excel ist leider noch ziemliches Neuland. So sieht es bis jetzt aus. Sicherlich verbesserbar :-(
Public Sub Diagramm_Summen_Zeilrufnummern()
Dim Bereich As Range, s As String
Set Bereich = Range("B10:E21, G10:G21")
s = ActiveSheet.Name
If Range("A1") = "Summen (Zielrufnummern)" Then
Charts.Add
ActiveChart.ChartType = xl3DColumn
ActiveChart.SetSourceData Source:=Bereich, PlotBy:=xlRows
ActiveChart.HasTitle = True
ActiveChart.ChartTitle.Text = s
ActiveChart.SeriesCollection(1).Select
ActiveChart.SeriesCollection(1).ApplyDataLabels AutoText:=True, LegendKey:= _
False, ShowSeriesName:=False, ShowCategoryName:=False, ShowValue:=True, _
ShowPercentage:=False, ShowBubbleSize:=False
ActiveChart.SeriesCollection(2).Select
ActiveChart.SeriesCollection(2).ApplyDataLabels AutoText:=True, LegendKey:= _
False, ShowSeriesName:=False, ShowCategoryName:=False, ShowValue:=True, _
ShowPercentage:=False, ShowBubbleSize:=False
ActiveChart.SeriesCollection(3).Select
ActiveChart.SeriesCollection(3).ApplyDataLabels AutoText:=True, LegendKey:= _
False, ShowSeriesName:=False, ShowCategoryName:=False, ShowValue:=True, _
ShowPercentage:=False, ShowBubbleSize:=False
ActiveChart.SeriesCollection(4).Select
ActiveChart.SeriesCollection(4).ApplyDataLabels AutoText:=True, LegendKey:= _
False, ShowSeriesName:=False, ShowCategoryName:=False, ShowValue:=True, _
ShowPercentage:=False, ShowBubbleSize:=False
ActiveChart.SeriesCollection(5).Select
ActiveChart.SeriesCollection(5).ApplyDataLabels AutoText:=True, LegendKey:= _
False, ShowSeriesName:=False, ShowCategoryName:=False, ShowValue:=True, _
ShowPercentage:=False, ShowBubbleSize:=False
ActiveChart.SeriesCollection(6).Select
ActiveChart.SeriesCollection(6).ApplyDataLabels AutoText:=True, LegendKey:= _
False, ShowSeriesName:=False, ShowCategoryName:=False, ShowValue:=True, _
ShowPercentage:=False, ShowBubbleSize:=False
ActiveChart.SeriesCollection(7).Select
ActiveChart.SeriesCollection(7).ApplyDataLabels AutoText:=True, LegendKey:= _
False, ShowSeriesName:=False, ShowCategoryName:=False, ShowValue:=True, _
ShowPercentage:=False, ShowBubbleSize:=False
ActiveChart.SeriesCollection(8).Select
ActiveChart.SeriesCollection(8).ApplyDataLabels AutoText:=True, LegendKey:= _
False, ShowSeriesName:=False, ShowCategoryName:=False, ShowValue:=True, _
ShowPercentage:=False, ShowBubbleSize:=False
ActiveChart.SeriesCollection(9).Select
ActiveChart.SeriesCollection(9).ApplyDataLabels AutoText:=True, LegendKey:= _
False, ShowSeriesName:=False, ShowCategoryName:=False, ShowValue:=True, _
ShowPercentage:=False, ShowBubbleSize:=False
ActiveChart.SeriesCollection(10).Select
ActiveChart.SeriesCollection(10).ApplyDataLabels AutoText:=True, LegendKey:= _
False, ShowSeriesName:=False, ShowCategoryName:=False, ShowValue:=True, _
ShowPercentage:=False, ShowBubbleSize:=False
ActiveChart.SeriesCollection(11).Select
ActiveChart.SeriesCollection(11).ApplyDataLabels AutoText:=True, LegendKey:= _
False, ShowSeriesName:=False, ShowCategoryName:=False, ShowValue:=True, _
ShowPercentage:=False, ShowBubbleSize:=False
ActiveChart.SeriesCollection(12).Select
ActiveChart.SeriesCollection(12).ApplyDataLabels AutoText:=True, LegendKey:= _
False, ShowSeriesName:=False, ShowCategoryName:=False, ShowValue:=True, _
ShowPercentage:=False, ShowBubbleSize:=False
'ActiveChart.SeriesCollection(1).Select
ActiveChart.SeriesCollection(1).DataLabels.Select
ActiveChart.SeriesCollection(1).Points(2).DataLabel.Select
Selection.AutoScaleFont = True
With Selection.Font
.Name = "Arial"
.FontStyle = "Standard"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = True
.Underline = xlUnderlineStyleNone
.ColorIndex = 2
.Background = xlAutomatic
End With
ActiveChart.SeriesCollection(1).Points(3).DataLabel.Select
Selection.AutoScaleFont = True
With Selection.Font
.Name = "Arial"
.FontStyle = "Standard"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = True
.Underline = xlUnderlineStyleNone
.ColorIndex = 2
.Background = xlAutomatic
End With
ActiveChart.SeriesCollection(1).Points(4).DataLabel.Select
Selection.AutoScaleFont = True
With Selection.Font
.Name = "Arial"
.FontStyle = "Standard"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = True
.Underline = xlUnderlineStyleNone
.ColorIndex = 2
.Background = xlAutomatic
End With
ActiveChart.SeriesCollection(2).Points(2).DataLabel.Select
Selection.AutoScaleFont = True
With Selection.Font
.Name = "Arial"
.FontStyle = "Standard"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = True
.Underline = xlUnderlineStyleNone
.ColorIndex = 2
.Background = xlAutomatic
End With
'ActiveChart.PlotArea.Select
ActiveChart.SeriesCollection(2).Points(3).DataLabel.Select
Selection.AutoScaleFont = True
With Selection.Font
.Name = "Arial"
.FontStyle = "Standard"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = True
.Underline = xlUnderlineStyleNone
.ColorIndex = 2
.Background = xlAutomatic
End With
'ActiveChart.PlotArea.Select
ActiveChart.SeriesCollection(2).Points(4).DataLabel.Select
Selection.AutoScaleFont = True
With Selection.Font
.Name = "Arial"
.FontStyle = "Standard"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = True
.Underline = xlUnderlineStyleNone
.ColorIndex = 2
.Background = xlAutomatic
End With
'ActiveChart.PlotArea.Select
ActiveChart.SeriesCollection(3).Points(2).DataLabel.Select
Selection.AutoScaleFont = True
With Selection.Font
.Name = "Arial"
.FontStyle = "Standard"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = True
.Underline = xlUnderlineStyleNone
.ColorIndex = 2
.Background = xlAutomatic
End With
'ActiveChart.PlotArea.Select
ActiveChart.SeriesCollection(3).Points(3).DataLabel.Select
Selection.AutoScaleFont = True
With Selection.Font
.Name = "Arial"
.FontStyle = "Standard"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = True
.Underline = xlUnderlineStyleNone
.ColorIndex = 2
.Background = xlAutomatic
End With
'ActiveChart.PlotArea.Select
ActiveChart.SeriesCollection(3).Points(4).DataLabel.Select
Selection.AutoScaleFont = True
With Selection.Font
.Name = "Arial"
.FontStyle = "Standard"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = True
.Underline = xlUnderlineStyleNone
.ColorIndex = 2
.Background = xlAutomatic
End With
'ActiveChart.PlotArea.Select
ActiveChart.SeriesCollection(4).Points(2).DataLabel.Select
Selection.AutoScaleFont = True
With Selection.Font
.Name = "Arial"
.FontStyle = "Standard"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = True
.Underline = xlUnderlineStyleNone
.ColorIndex = 2
.Background = xlAutomatic
End With
ActiveChart.SeriesCollection(4).Points(3).DataLabel.Select
Selection.AutoScaleFont = True
With Selection.Font
.Name = "Arial"
.FontStyle = "Standard"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = True
.Underline = xlUnderlineStyleNone
.ColorIndex = 2
.Background = xlAutomatic
End With
ActiveChart.SeriesCollection(4).Points(4).DataLabel.Select
Selection.AutoScaleFont = True
With Selection.Font
.Name = "Arial"
.FontStyle = "Standard"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = True
.Underline = xlUnderlineStyleNone
.ColorIndex = 2
.Background = xlAutomatic
End With
ActiveChart.SeriesCollection(5).Points(2).DataLabel.Select
Selection.AutoScaleFont = True
With Selection.Font
.Name = "Arial"
.FontStyle = "Standard"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = True
.Underline = xlUnderlineStyleNone
.ColorIndex = 2
.Background = xlAutomatic
End With
ActiveChart.SeriesCollection(5).Points(3).DataLabel.Select
Selection.AutoScaleFont = True
With Selection.Font
.Name = "Arial"
.FontStyle = "Standard"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = True
.Underline = xlUnderlineStyleNone
.ColorIndex = 2
.Background = xlAutomatic
End With
ActiveChart.SeriesCollection(5).Points(4).DataLabel.Select
Selection.AutoScaleFont = True
With Selection.Font
.Name = "Arial"
.FontStyle = "Standard"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = True
.Underline = xlUnderlineStyleNone
.ColorIndex = 2
.Background = xlAutomatic
End With
ActiveChart.SeriesCollection(6).Points(2).DataLabel.Select
Selection.AutoScaleFont = True
With Selection.Font
.Name = "Arial"
.FontStyle = "Standard"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = True
.Underline = xlUnderlineStyleNone
.ColorIndex = 2
.Background = xlAutomatic
End With
ActiveChart.SeriesCollection(6).Points(3).DataLabel.Select
Selection.AutoScaleFont = True
With Selection.Font
.Name = "Arial"
.FontStyle = "Standard"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = True
.Underline = xlUnderlineStyleNone
.ColorIndex = 2
.Background = xlAutomatic
End With
ActiveChart.SeriesCollection(6).Points(4).DataLabel.Select
Selection.AutoScaleFont = True
With Selection.Font
.Name = "Arial"
.FontStyle = "Standard"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = True
.Underline = xlUnderlineStyleNone
.ColorIndex = 2
.Background = xlAutomatic
End With
ActiveChart.SeriesCollection(7).Points(2).DataLabel.Select
Selection.AutoScaleFont = True
With Selection.Font
.Name = "Arial"
.FontStyle = "Standard"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = True
.Underline = xlUnderlineStyleNone
.ColorIndex = 2
.Background = xlAutomatic
End With
ActiveChart.SeriesCollection(7).Points(3).DataLabel.Select
Selection.AutoScaleFont = True
With Selection.Font
.Name = "Arial"
.FontStyle = "Standard"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = True
.Underline = xlUnderlineStyleNone
.ColorIndex = 2
.Background = xlAutomatic
End With
ActiveChart.SeriesCollection(7).Points(4).DataLabel.Select
Selection.AutoScaleFont = True
With Selection.Font
.Name = "Arial"
.FontStyle = "Standard"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = True
.Underline = xlUnderlineStyleNone
.ColorIndex = 2
.Background = xlAutomatic
End With
ActiveChart.SeriesCollection(8).Points(2).DataLabel.Select
Selection.AutoScaleFont = True
With Selection.Font
.Name = "Arial"
.FontStyle = "Standard"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = True
.Underline = xlUnderlineStyleNone
.ColorIndex = 2
.Background = xlAutomatic
End With
ActiveChart.SeriesCollection(8).Points(3).DataLabel.Select
Selection.AutoScaleFont = True
With Selection.Font
.Name = "Arial"
.FontStyle = "Standard"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = True
.Underline = xlUnderlineStyleNone
.ColorIndex = 2
.Background = xlAutomatic
End With
ActiveChart.SeriesCollection(8).Points(4).DataLabel.Select
Selection.AutoScaleFont = True
With Selection.Font
.Name = "Arial"
.FontStyle = "Standard"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = True
.Underline = xlUnderlineStyleNone
.ColorIndex = 2
.Background = xlAutomatic
End With
ActiveChart.SeriesCollection(9).Points(2).DataLabel.Select
Selection.AutoScaleFont = True
With Selection.Font
.Name = "Arial"
.FontStyle = "Standard"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = True
.Underline = xlUnderlineStyleNone
.ColorIndex = 2
.Background = xlAutomatic
End With
ActiveChart.SeriesCollection(9).Points(3).DataLabel.Select
Selection.AutoScaleFont = True
With Selection.Font
.Name = "Arial"
.FontStyle = "Standard"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = True
.Underline = xlUnderlineStyleNone
.ColorIndex = 2
.Background = xlAutomatic
End With
ActiveChart.SeriesCollection(9).Points(4).DataLabel.Select
Selection.AutoScaleFont = True
With Selection.Font
.Name = "Arial"
.FontStyle = "Standard"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = True
.Underline = xlUnderlineStyleNone
.ColorIndex = 2
.Background = xlAutomatic
End With
ActiveChart.SeriesCollection(10).Points(2).DataLabel.Select
Selection.AutoScaleFont = True
With Selection.Font
.Name = "Arial"
.FontStyle = "Standard"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = True
.Underline = xlUnderlineStyleNone
.ColorIndex = 2
.Background = xlAutomatic
End With
ActiveChart.SeriesCollection(10).Points(3).DataLabel.Select
Selection.AutoScaleFont = True
With Selection.Font
.Name = "Arial"
.FontStyle = "Standard"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = True
.Underline = xlUnderlineStyleNone
.ColorIndex = 2
.Background = xlAutomatic
End With
ActiveChart.SeriesCollection(10).Points(4).DataLabel.Select
Selection.AutoScaleFont = True
With Selection.Font
.Name = "Arial"
.FontStyle = "Standard"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = True
.Underline = xlUnderlineStyleNone
.ColorIndex = 2
.Background = xlAutomatic
End With
ActiveChart.SeriesCollection(11).Points(2).DataLabel.Select
Selection.AutoScaleFont = True
With Selection.Font
.Name = "Arial"
.FontStyle = "Standard"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = True
.Underline = xlUnderlineStyleNone
.ColorIndex = 2
.Background = xlAutomatic
End With
ActiveChart.SeriesCollection(11).Points(3).DataLabel.Select
Selection.AutoScaleFont = True
With Selection.Font
.Name = "Arial"
.FontStyle = "Standard"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = True
.Underline = xlUnderlineStyleNone
.ColorIndex = 2
.Background = xlAutomatic
End With
ActiveChart.SeriesCollection(11).Points(4).DataLabel.Select
Selection.AutoScaleFont = True
With Selection.Font
.Name = "Arial"
.FontStyle = "Standard"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = True
.Underline = xlUnderlineStyleNone
.ColorIndex = 2
.Background = xlAutomatic
End With
ActiveChart.SeriesCollection(12).Points(2).DataLabel.Select
Selection.AutoScaleFont = True
With Selection.Font
.Name = "Arial"
.FontStyle = "Standard"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = True
.Underline = xlUnderlineStyleNone
.ColorIndex = 2
.Background = xlAutomatic
End With
ActiveChart.SeriesCollection(12).Points(3).DataLabel.Select
Selection.AutoScaleFont = True
With Selection.Font
.Name = "Arial"
.FontStyle = "Standard"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = True
.Underline = xlUnderlineStyleNone
.ColorIndex = 2
.Background = xlAutomatic
End With
ActiveChart.SeriesCollection(12).Points(4).DataLabel.Select
Selection.AutoScaleFont = True
With Selection.Font
.Name = "Arial"
.FontStyle = "Standard"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = True
.Underline = xlUnderlineStyleNone
.ColorIndex = 2
.Background = xlAutomatic
End With
ActiveChart.Walls.Select
ActiveChart.Corners.Select
With ActiveChart
.Elevation = 13
.Rotation = 71
End With
ActiveChart.PlotArea.Select
Selection.Width = 670
Selection.Height = 398
ActiveChart.ChartArea.Select
ActiveChart.PlotArea.Select
Selection.Top = 27
Selection.Width = 699
Selection.Height = 419
ActiveChart.Legend.Select
Selection.Top = 127
Selection.Height = 210
Else
MsgBox ("Bitte die Tabelle -Summen(Zielrufnummern)- öffnen"), vbOKOnly, ("Falsche _
Ausgangstabelle")
End If
End Sub