excel 2007 diagramm balken per vba bearbeiten

N'Abend allerseits,

ich könnte gut eure Hilfe gebrauchen. Mein Problem ist das ich 8 Spalten (jeweils mit Überschriften) habe und die unten stehenden Summenwerte werden in einem 2D-Balkendiagramm dargestellt. Diese Grundstruktur ist in jedem Arbeitsblatt dieselbe. Nur im jeweils nächsten Arbeitsblatt kann es sein, dass sich die Überschriften der 8 Spalten ändern, sie tauschen die Positionen.
Da ich es jetzt aber so eingerichtet habe, dass jede Überschrift auch ihre eigene Datensatzfarbe hat, z.b. zelle B1 = p1 > im diagramm rot, zelle C1 = p2 > im diagramm blau, usw. bleibt rot die farbe für den Summenwert der spalte B.
Also kurz und knapp erzählt, ich möchte, dass wenn "p1" nicht mehr als überschrift in B1 steht, sondern z.b. in D1, dann möchte ich auch das die datensatzfarbe von "p1" für den summenwert aus der neuen spalte steht, sprich nach meinem beispiel die spalte D.
Außerdem kommt es eben vor, dass ich nicht nur einfache Farben gewählt habe, sondern auch graduelle Füllungen oder auch Bilder.
Ich habe das schon versucht selber mit dem Makrorekorder zu machen und mit dem code weiterzuarbeiten, nur zeigt er mir im editor die farbauswahl usw. nicht an. Bei ActiveChart.SeriesCollection(1).Select hört er auf mitzuschreiben.

Ich hoffe, Ihr habt meine Gedanken verstanden und wisst, worauf ich hinaus will.

Ich bedanke mich schon mal im Voraus bei Euch,
MZurmuehlen

 

Hi,

da ich deine genauen Formatierungen nicht kenne, hier mal ein prinzipieller Code:

Code:

Sub DiaFormatieren()
    Dim inFormat As Integer
    With ActiveSheet.ChartObjects(1).Chart
        For inFormat = 1 To .SeriesCollection.Count
            With .SeriesCollection(inFormat)
            Select Case .Name
                Case "p1"
                    .Interior.ColorIndex = 6
                Case "p2b"
                    .Fill.UserPicture PictureFile:= _
                        "D:\Eigene Bilder\03010003.jpg", PictureFormat:=xlStretch, _
                        PicturePlacement:=xlAllFaces
                Case "p3"
                    With .Fill
                        .TwoColorGradient Style:=msoGradientVertical, Variant:=4
                        .Visible = True
                        .ForeColor.SchemeColor = 4
                        .BackColor.SchemeColor = 10
                    End With
                Case "p4"
                    .Interior.ColorIndex = 3
            End Select
            End With
        Next inFormat
    End With
End Sub

 

hi,
ich wollte mich nochmal bedanken, bis jetzt hilft mir das auf jeden fall weiter.

ich hab jetzt bloß noch keine ahnung wie der vba-code für die folgenden graduellen füllungen aussieht: einmal "weizen" mit nem winkel von 45° und "feuer" mit winkel 90°. beide sind vom typ Linear (falls das wichtig ist).
die restlichen farben sind (einfarbig): grün, "weiß, hintergrund 1, dunkler 15%", rot, hellblau & blau.

ich wüsste dann eventuell auch noch gerne was die einstellungen bedeuten:
"PicturePlacement:=xlAllFaces" bei der sache mit dem bild,
und die sachen halt bei der graduellen füllung.
dann würde ich das dann auch verstehen und eventuell in zukunft selber zusammenbasteln können .

Gruß
MZurmuehlen

PS: zur not könnte ich mir die einfachen füllfarben auch selbst rausfinden, nicht das ich faul bin, es würde mir halt die arbeit verkürzen .

 

Hallo,
vielleicht kann Dir >>diese<< Seite bei der Auswahl der Farben weiterhelfen.

 

Hi,

das Problem ist, dass Excel2007 - wie du schon festgestellt hast - diesen Code (wie eine Reihe weiterer Codes beim Formatieren von Objekten) nicht mehr aufzeichnet. Das was ich gepostet habe, sind die Einstellungen, wie sie unter Excel < 2007 gelten. Du hast nun leider nur Möglichkeit, anhand des von mir geposteten prinzipiellen Codes dich anhand der Stichworte durch die VBA-Hilfe von Ecel2007 durchzuforsten. Ich könnte dir dabei vielleicht behilflich sein, wenn ich deine Arbeitsmappe kennen würde, aber dazu müsstest du sie hochladen. Nur so kann man sehen, welche Einstellungen du benutzt.

 

hi beverly,
ich habe deinen rat befolgt und erstmal meine arbeitsmappe hochgeladen.

gruß MZurmuehlen

 

Hi,

so sollte es gehen:

Code:

Sub DiaFormatieren()
    Dim inFormat As Integer
    With ActiveSheet.ChartObjects(1).Chart.SeriesCollection(1)
        .ApplyDataLabels ShowSeriesName:=False, ShowCategoryName:=True, _
            ShowValue:=False, ShowPercentage:=False, ShowBubbleSize:=False
        For inFormat = 1 To .Points.Count
            With .Points(inFormat)
                Select Case .DataLabel.Caption
                    Case "p1"
                        .Interior.Color = 5287936
                    Case "p2"
                        .Fill.PresetGradient Style:=msoGradientHorizontal, variant:=1, PresetGradientType:=msoGradientFire
                    Case "p3"
                        .Interior.Color = 15773696
                    Case "p4"
                        .Fill.UserPicture PictureFile:= _
                            "C:\Test\Bild2.gif", PictureFormat:=xlStretch, _
                            PicturePlacement:=xlAllFaces
                    Case "p5"
                        .Fill.PresetGradient Style:=msoGradientDiagonalUp, variant:=1, PresetGradientType:=msoGradientWheat
                    Case "p6"
                        .Interior.Color = 14277081
                    Case "p7"
                        .Interior.Color = 12611584
                    Case "p8"
                       .Fill.UserPicture PictureFile:= _
                            "C:\Test\Bild2.gif", PictureFormat:=xlStretch, _
                            PicturePlacement:=xlAllFaces
                End Select
            End With
        Next inFormat
        .DataLabels.Delete
    End With
End Sub

Bitte Pfad und Bildname anpassen. Beachte bitte, dass die Bilder nicht im JPG-Format gespeichert sein dürfen. BMP, GIF sind auf jeden Fall möglich, den Rest habe ich nicht getestet.

 

hi beverly,
sorry das diagramm in der arbeitsmappe war nicht wie in meinem original.
deswegen hab ich das grad nochmal korrigiert, is also jetz richtig.
(das original wollte ich nich hochladen, weil das doch ziemlich gro&#223; ist, excel 2007 is mir schon ab und zu abgest&#252;rzt)

ich habs jetz nochmal hochgeladen.

gru&#223; mzurmuehlen

PS: die bilder, die ich verwende sind im bmp-format. also kein problem

 

Hi,

der einfachste Weg, eine abgespeckte Arbeitsmappe herzustellen ist, dass man das Original unter einem anderen Namen abspeichert und nicht relevante Daten löscht. Das erspart ein Neuerstellen und gewährleistet, dass die Kopie in allen relevanten Dingen dem Original entspricht.

Code:

Sub DiaFormatieren()
    Dim inFormat As Integer
    With ActiveSheet.ChartObjects(1).Chart
        For inFormat = 1 To .SeriesCollection.Count
            With .SeriesCollection(inFormat)
                Select Case .Name
                    Case "p1"
                        .Interior.Color = 5287936
                    Case "p2"
                        .Fill.PresetGradient Style:=msoGradientHorizontal, variant:=1, PresetGradientType:=msoGradientFire
                    Case "p3"
                        .Interior.Color = 15773696
                    Case "p4"
                        .Fill.UserPicture PictureFile:= _
                            "C:\Test\Bild1.gif", PictureFormat:=xlStretch, _
                            PicturePlacement:=xlAllFaces
                    Case "p5"
                        .Fill.PresetGradient Style:=msoGradientDiagonalUp, variant:=1, PresetGradientType:=msoGradientWheat
                    Case "p6"
                        .Interior.Color = 14277081
                    Case "p7"
                        .Interior.Color = 12611584
                    Case "p8"
                       .Fill.UserPicture PictureFile:= _
                            "C:\Test\Bild2.gif", PictureFormat:=xlStretch, _
                            PicturePlacement:=xlAllFaces
                End Select
            End With
        Next inFormat
    End With
End Sub

 

hi beverly,

der code funzt soweit, nur bei den bildern fliegt er jedes mal mit folgender fehlermeldung raus (anhang).

hab das mal gegoogelt, sagt mir aber jetz nich wirklich was, vor allem weils da um activex-elemente(?) gehen soll und ich hab sowas nich in meiner arbeitsmappe drin.

gruß mzurmuehlen

 

Hi,

dieser Laufzeitfehler tritt auf, wenn das verwendete Element nicht vorhanden ist. Der Code funktioniert auf meinem Rechner in deiner Arbeitsmappe mit den im Code vorgegebenen Bildnamen in den im Code angegebenen Speicherpfaden.

 

hi beverly,

dann wäre mein problem, dass ich die bildpfade schon angepasst habe und ich habe sie auch nochmal auf ihre richtigkeit überprüft. daran kanns also nich liegen. ich habe auch nur den pfad angepasst, ansonsten habe ich an deinem beispiel nix geändert.

gruß mzurmuehlen

 

Hi,

ich habe den Code jetzt extra noch einmal mit BMP-Bildern getestet und er funktioniert genau so wie mit den GIF-Bildern. Ich lade deine Mappe mit meinem Code mal hoch, vielleicht ist in deiner Originaldatei ja doch irgendetwas anders als in dieser.

 

hi,

ich hab jetz eine tabelle aus der arbeitsmappe herauskopiert.
er stockt weiter nur bei der bild anweisung, obwohl der pfad nich falsch is, denn wenn ich ihn bei start>ausführen eingebe öffnet windows mir das bild.

Vielleicht hilft dir das jetzt.

gruß MZurmuehlen

 

Hi,

das Problem liegt darin, dass 2 deiner Diagramme keine Säulen- sondern Liniendiagramme sind. Die verschiedenen Diagrammtyen erfordern eine unterschiedliche Handhabung - nicht nur bei der Erstellung/Formatierung von Hand, sondern insbesondere auch in VBA. Deshalb ist es um so wichtiger, dass die Fragestellung korrekt auf die Bedingungen in der Arbeitsmappe angepasst ist.

In der Version 2007 gibt es - im Gegensatz zu den Vorgängerversionen - die Möglichkeit, auch Datenpunkte des Diagrammtyps Linie mit Grafiken zu formatieren. Offensichtlich ist das nun ein Bug, dass man dies zwar von Hand, nicht jedoch per VBA direkt machen kann - deshalb diese indifferente Fehlermeldung.

Ausweg: den Diagrammtyp der Datenreihe zuerst in gruppierte Säulen umwandeln, die Grafik einfügen und anschließend den Diagrammtyp wieder in Linie zurück wandeln

Code:

               ElseIf (.SeriesCollection.Count = 8) Then
                    For intSeries = 1 To .SeriesCollection.Count
                        With .SeriesCollection(intSeries)
                            If ActiveSheet.ChartObjects(intDiag).Chart.Name = "Tabelle 1 Diagramm 3" Or _
                                ActiveSheet.ChartObjects(intDiag).Chart.Name = "Tabelle 1 Diagramm 4" Then
                                .ChartType = xlColumnClustered
                            End If
                            Select Case .Name
                                Case "p1"
                                    .Fill.UserPicture PictureFile:="C:\Test\Bild1.bmp", PictureFormat:=xlStretch, PicturePlacement:=xlAllFaces
                                Case "p2"
                                    .Fill.PresetGradient Style:=msoGradientDiagonalUp, Variant:=1, PresetGradientType:=msoGradientWheat
                                Case "p3"
                                    .Interior.Color = 5287936
                                Case "p4"
                                    .Fill.PresetGradient Style:=msoGradientHorizontal, Variant:=1, PresetGradientType:=msoGradientFire
                                Case "p5"
                                    .Interior.Color = 112611584
                                Case "p6"
                                    .Interior.Color = 15773696
                                Case "p7"
                                    .Fill.UserPicture PictureFile:="C:\Test\Bild2.bmp", PictureFormat:=xlStretch, PicturePlacement:=xlAllFaces
                                Case "p8"
                                    .Interior.Color = 12611584
                            End Select
                            If ActiveSheet.ChartObjects(intDiag).Chart.Name = "Tabelle 1 Diagramm 3" Or _
                                ActiveSheet.ChartObjects(intDiag).Chart.Name = "Tabelle 1 Diagramm 4" Then
                                .ChartType = xlLineMarkers
                            End If
                        End With
                    Next intSeries
                End If

 

hi,

code angepasst und trotzdem nix.
der bug geht mir langsam auf den sack.
gibts eventuell noch einen zweiten ausweg?

gruß mzurmuehlen

 

Hi,

letzter Versuch.

Ich habe deinen Code außerdem noch ein wenig optimiert, denn in VBA kann zu 99% auf Select/Activate verzichtet werden.

Nur eines ist mir nicht ganz klar - wozu diese ganze aufwendige Formatierung der Datenreihen? Wenn ich das richtig gesehen habe, bleibt doch die Reihenfolge p1 bis p8 im Tabellenblatt immer gleich - welchen Zweck hätte es, sie zu ändern?

 

hi beverly,

diese liste ist so eine art punkte wertung. je nachdem wie viele punkte am ende in der summe bei p1 bis p8 stehen ändert sich auf dem nächsten tabellen blatt die reihenfolge der auflistung.

also aus irgendeinem mir unerfindlichem grund funzt die letzte lösung (bei mir zumindest) auch nich. so unbedingt notwendig war diese sache in vba auch nich, manuell sind das nich mehr als knapp 5 minuten. daher lass ich das jetz, und mach das manuell.
trotzdem möchte ich mich bei dir bzw. euch für eure hilfe nochmal bedanken.
warscheinlich wäre das problem gar nicht erst aufgetaucht wenn microsoft den makrorekorder wie z.b. bei version 2003 auch die sachen mit den diagrammen aufnehmen hätte lassen. ich hoffe das renkt sich in der nächsten version, excel 2010 wirds wohl werden, auch wieder ein.

gruß mzurmuehlen

 

Hi,

hast du SP1 installiert? Mit diesem Servicepack wurde eine ganze Reihe von Problemen insbesondere auch mit Diagrammen behoben.

Was verstehst du unter "funzt nicht" - löst der Code einen Fehler aus oder macht er nicht das was er machen soll? Bei mir ist es so, dass Excel sehr langsam reagiert und es dauert immer eine kleine Weile, bis das Bild im Datenpunkt zu sehen ist. Das hängt aber einfach damit zusammen, dass das Bild erst ins Tabellenblatt eingefügt, kopiert, in die Datenreihe eingefügt und dann wieder gelöscht wird.

Was Excel2010 betrifft so kann ich dir eine frohe Botschaft verkünden - die Formatierung von eingebetteten Objekten wie Shapes und auch Diagrammen wird wieder aufgezeichnet. Ich habe mal von jemandem, der bereits Excel2010 testweise installiert hat, den Code für das Einfügen einer Grafik in eine Datenreihe aufzeichnen lassen - er sieht so aus (schon angepasst an den Code in deiner Arbeitsmappe):

Code:

.format.Fill.Visible = msoTrue
.format.Fill.UserPicture "C:\Test\bild1.bmp"

Der Unterschied zu meinem ursprünglichen Code ist, dass anstelle von .Fill dieses verwendet wird: .Format.Fill - aber das muss man erst einmal wissen wenn es nirgendwo beschrieben ist.