VBA Datenquelle Diagramm anpassen

Informationen und Beispiele zu den hier genannten Dialog-Elementen:
TextBox
Bild

Betrifft: VBA Datenquelle Diagramm anpassen
von: Moe
Geschrieben am: 19.11.2015 15:55:52

Hallo Leute
Ich habe in meinem Excel-File 4 Registerblätter. Zwei Registerblätter mit vielen Rohdaten ("Datencluster_Testszenarien" & "Datencluster_GVZ") und zwei Registerblätter mit vielen Diagrammen (Output_Diagramme_Test" & "Output_Diagramme_GVZ"), welche sich auf die Daten der anderen Registerblätter bezieht.
Nun habe ich alle Diagramme für die Testszenarien erstellt. Die Diagramme für die Daten _GVZ sind identisch (Range der Quelle bleibt), jedoch muss ich der Name des Registerblattes in der Quelle ändern. Da es hunderte Quellen sind, möchte ich das mit VBA machen.
Aufgabe: Bei jeder Datenreihe im aktiven Diagramm soll es sowohl beim Reihenname, wie auch bei den Reihenwerte das Registerblatt von "Datencluster_Testszenarien" auf "Datencluster_GVZ" ersetzen. Wie erwähnt bleibt der Range der Zellen identisch.
Ist das möglich? Kann mir jemand weiterhelfen?
Danke und Gruss
Moe

Bild

Betrifft: AW: VBA Datenquelle Diagramm anpassen
von: Beverly
Geschrieben am: 19.11.2015 18:05:00
Hi,
nach diesem Prinzip:

Sub DiaAnpassen()
    Dim chrDia As ChartObject
    Dim serReihe As Series
    ' Schleife über alle Diagramme in Tabelle5
    For Each chrDia In Worksheets("Tabelle5").ChartObjects
        For Each serReihe In chrDia.Chart.SeriesCollection
            ' in Datenreihenformel Tabellenbezug von Tabelle1 auf Tabelle2 ändern
            serReihe.Formula = Application.Substitute(serReihe.Formula, "Tabelle1", "Tabelle2")
        Next serReihe
    Next chrDia
End Sub

Tabellennamen musst du entsprechend anpassen.



Bild

Betrifft: AW: VBA Datenquelle Diagramm anpassen
von: Moe
Geschrieben am: 20.11.2015 14:20:48
Hallo Karin
Danke für dein Skribt! In einer Testdatei funktioniert der Code einwandfrei. In meinem Auswertungsfile jedoch nicht. Ich habe den Grund leider nicht gefunden...
Hier ist ein Ausschnitt aus meinem File: https://www.herber.de/bbs/user/101669.xlsm
Bei der kompletten Datei ist der Aufbau identisch, nur sind es zahlreiche Fälle.
Jemand ein Tipp?
Gruss,
Moe

Bild

Betrifft: AW: VBA Datenquelle Diagramm anpassen
von: Beverly
Geschrieben am: 20.11.2015 15:49:11
Hi,
die Ursache liegt darin, dass du mindestens 1 Diagramm hast, das #REF! in den Zellbezügen zu stehen hat, weshalb die Datenreihenformel nicht ausgelesen werden kann und eine Fehler auslöst. Das betrifft z.B. das "Diagramm 3" in Tabelle "Output_Diagramme_GVZ" - die linke obere Ecke des Diagramms liegt auf Zelle B26 und du kannst es nicht sehen, weil es eine Größe von 0 hat. Lass mal diesen Code laufen, dann siehst du es:

Sub DiaGroesse()
    Application.Goto reference:=ActiveSheet.ChartObjects("Diagramm 3").TopLeftCell, _
        scroll:=True
    With ActiveSheet.ChartObjects("Diagramm 3")
        .Width = 350
        .Height = 250
    End With
End Sub

Entweder, du lässt erst einmal einen Code über alle Tabellenblätter laufen und löschst die Diagramme mit einer Größe von 0 (falls du sie tatsächlich nicht benötigst), oder du ergänzt den Code aus meinem vorhergehenden Beitrag um eine Prüfung, ob das jeweilige Diagramm eine Größe von > 0 hat:
Sub DiaRegisterblattWechseln()
    Dim chrDia As ChartObject
    Dim serReihe As Series
    ' Schleife über alle Diagramme in "Output_Diagramme_GVZ"
    For Each chrDia In Worksheets("Output_Diagramme_GVZ").ChartObjects
        If chrDia.Width > 0 And chrDia.Height > 0 Then
            For Each serReihe In chrDia.Chart.SeriesCollection
                ' in Datenreihenformel Tabellenbezug von "Datencluster_Testszenarien"
                ' auf "Datencluster_GVZ" ändern
                serReihe.Formula = Application.Substitute(serReihe.Formula, _
                    "Datencluster_Testszenarien", "Datencluster_GVZ")
            Next serReihe
        End If
    Next chrDia
End Sub



Bild

Betrifft: AW: VBA Datenquelle Diagramm anpassen
von: Moe
Geschrieben am: 20.11.2015 15:58:20
Vielen lieben Dank! Funktioniert perfekt!
Aber ich habe keine Ahnung von wo dieses Diagramm mit Grösse 0 kommt... ;)

Bild

Betrifft: AW: VBA Datenquelle Diagramm anpassen
von: Moe
Geschrieben am: 20.11.2015 16:05:00
Trotzdem nochmals eine Frage: Würde dieser, bzw. ein leicht angepasster, Code auch für die Texfelder mit den Zellbezügen gehen? Mit dem genau gleichen Ziel...

Bild

Betrifft: AW: VBA Datenquelle Diagramm anpassen
von: Beverly
Geschrieben am: 20.11.2015 16:27:00
Hi (dies ist die Anrede im Beitrag),
(hier folgt der Beitragstext)
Das ginge nach diesem Prinzip:

Sub Textfelder()
    Dim txtElement As TextBox
    For Each txtElement In ActiveSheet.TextBoxes
        If InStr(txtElement.Formula, "Datencluster_Testszenarien") > 0 Then _
            txtElement.Formula = Application.Substitute(txtElement.Formula, _
            "Datencluster_Testszenarien", "Datencluster_GVZ")
    Next txtElement
End Sub

(und am Ende eines Beitrages folgt ein Gruß mit einem Namen. Ein derartiger Beitragsaufbau fördert die Bereitschaft der Helfer, sich mit den Problemen der Fragesteller zu beschäftigen, denn sie sind ebenfalls Menschen und keine Compuer ;-) )



Bild

Betrifft: AW: VBA Datenquelle Diagramm anpassen
von: Moe
Geschrieben am: 23.11.2015 08:48:10
Hi Karin
Ich entschuldige mich für mein Beitragsaufbau und bedanke mich aber für deine super Hilfe! ;)
Der Code für die Textfelder funktioniert irgendwie noch nicht. Auch in einem Testfile wird das Registerblatt im Zellbezug nicht geändert.
Hier ist wieder ein kleiner Auszug aus meinem File https://www.herber.de/bbs/user/101739.xlsm
Erkennst du das Problem?
Gruss,
Moe

Bild

Betrifft: AW: VBA Datenquelle Diagramm anpassen
von: Beverly
Geschrieben am: 23.11.2015 10:29:28
Hi Moe,
der Code funktionert schon korrekt - als Beispiel gelbe Datenreihe im äußersten linken Diagramm
vor Codeausführung: =DATENREIHE(Datencluster_Testszenarien!$AC$4;Datencluster_Testszenarien!$AC$13:$AC$24;Datencluster_Testszenarien!$P$13:$P$24;1)
nach Codeausführung: =DATENREIHE(Datencluster_GVZ!$AO$4;Datencluster_GVZ!$AO$13:$AO$24;Datencluster_GVZ!$P$13:$P$24;3)
Dein Problem ist, dass es wiederum ein Diagramm gibt, welches du nicht sehen kannst (da seine Größe 0 ist) und dessen Datenreihen keinen Zellbezug haben.




Bild

Betrifft: AW: VBA Datenquelle Diagramm anpassen
von: Moe
Geschrieben am: 23.11.2015 11:06:43
Hi Karin
Der Code für die Diagramme funktioniert einwandfrei. Jedoch der Code für die Textfelder nicht. Es gibt keine Errormeldung, aber das Registerblatt in der Datenreihe wird nicht geändert...
Gruss
Moe

Bild

Betrifft: AW: VBA Datenquelle Diagramm anpassen
von: Beverly
Geschrieben am: 23.11.2015 11:21:49
Hi Moe,
sorry, hatte ich falsch verstanden, da du dich auf den Begriff "Datenreihe" beziehst - ein Textfeld hat keine Datenreihe sondern höchstens einen Zellbezug.
In deiner aktiven Tabelle gibt es kein Textfeld, welches einen Zellbezug enthält. Es gibt nur eins mit dem Inhalt "! = Überströmelement offen" und dieser ist fix eingetragen.




Bild

Betrifft: AW: VBA Datenquelle Diagramm anpassen
von: Moe
Geschrieben am: 23.11.2015 12:04:44
Hallo Karin
Doch, es hat einige Textfelder mit einem Zellbezug (alle Felder mit einem "!" links neben dem Diagramm "Volumenströme Treppenhaus, oder die Zahl beim "Öffnung DLE" unterhalb der beiden mittleren Diagrammen).
Gruss,
Moe

Bild

Betrifft: AW: VBA Datenquelle Diagramm anpassen
von: Beverly
Geschrieben am: 23.11.2015 13:08:22
Hi Moe,
1. die Textfelder oberhalb der Zeichnungsfläche der Diagramme sind die Diagrammtitel, keine eigentständigen Textfelder. Diesen kannst du die neuen Zellbezüge so zuweisen:

Sub DiaTitelWechseln()
    Dim chrDia As ChartObject
    ' Schleife über alle Diagramme in "Output_Diagramme_GVZ"
    For Each chrDia In Worksheets("Output_Diagramme_GVZ").ChartObjects
        chrDia.Chart.ChartTitle.Formula = Application.Substitute(chrDia.Chart.ChartTitle. _
Formula, _
            "Datencluster_Testszenarien", "Datencluster_GVZ")
    Next chrDia
End Sub
2. die Shapes mit den Zahlen am unteren Rand sind Gruppierungen, keine reinen Textfelder, deshalb muss man sie anders ansprechen:
Sub TxtRegisterblattWechseln()
    Dim shaElement As Shape
    Dim intZaehler As Integer
    For Each shaElement In ActiveSheet.Shapes
        ' Gruppierung
        If shaElement.Type = msoGroup Then
            For intZaehler = 1 To shaElement.GroupItems.Count
                ' Textfeld in einer Gruppierung
                If shaElement.GroupItems(intZaehler).Type = msoTextBox Then
                    If InStr(shaElement.GroupItems(intZaehler).OLEFormat.Object.Formula, _
                        "Datencluster_Testszenarien") > 0 Then _
                        shaElement.GroupItems(intZaehler).OLEFormat.Object.Formula = _
                        Application.Substitute(shaElement.GroupItems(intZaehler).OLEFormat. _
Object.Formula, _
                        "Datencluster_Testszenarien", "Datencluster_GVZ")
                End If
            Next intZaehler
        ' selbständige Textfelder
        ElseIf shaElement.Type = msoTextBox Then
            If InStr(shaElement.Formula, "Datencluster_Testszenarien") > 0 Then _
                shaElement.Formula = Application.Substitute(shaElement.Formula, _
                "Datencluster_Testszenarien", "Datencluster_GVZ")
        End If
    Next shaElement
End Sub



Bild

Betrifft: AW: VBA Datenquelle Diagramm anpassen
von: Moe
Geschrieben am: 23.11.2015 14:12:59
Hi Karin,
Du bist genial! Das mit den Diagrammtiteln funzt perfekt. Bei den Textfelder gibt es mir ca. in der Mitte des Worksheets den Laufzeitfehler 438 "Objekt unterstützt diese Eigenschaft oder Methode nicht". Vielleicht hat sich wieder irgend eine "fremdes Obejekt" eingeschlichen.
Wie könnte ich dieses erkennen oder direkt mit einer Bedingung "überspringen"?
Gruss,
Moe

Bild

Betrifft: AW: VBA Datenquelle Diagramm anpassen
von: Beverly
Geschrieben am: 23.11.2015 19:09:58
Hi Moe,
man könnte natürlich einfach mit On Error Resume Next arbeiten, aber das ist kein feine Programmierung, falls man den Fehler anders abfangen kann. An welcher Stelle im Code genau kommt denn der Fehler - im If-Zweig oder im ElseIf-Zweig?
In deiner hochgeladenen Arbeitsmappe habe ich diesen Fehler nicht erhalten - ich habe aber auch nur das aktivierte Tabellenblatt geprüft.




Bild

Betrifft: AW: VBA Datenquelle Diagramm anpassen
von: Moe
Geschrieben am: 24.11.2015 08:00:15
Hi Karin
Ja das könnte ich einbauen, aber wie du bereits erwähnt hast, ist es nicht gerade die beste Lösung. Der Error tritt im ElseIf-Zweig auf, bei der Zeiele "If InStr(shaElement.Formula, "Datencluster_Testszenarien" ..."
Hast du eine Idee?
Gruss,
Moe

Bild

Betrifft: AW: VBA Datenquelle Diagramm anpassen
von: Beverly
Geschrieben am: 24.11.2015 08:14:35
Hi Moe,
kannst du feststellen, um welches Shape es sich handelt und worin es sich von den anderen unterscheidet?
Falls nicht, dann kopiere die gesamte Tabelle in eine neue Mappe und lade sie hoch. Falls sie zu groß ist, dann lösche ein paar der Diagramme/Shapes, bei denen der Fehler nicht aufgetreten ist.




Bild

Betrifft: AW: VBA Datenquelle Diagramm anpassen
von: Moe
Geschrieben am: 24.11.2015 09:12:41
Hi Karin
Das mit dem Shape erkennen habe ich leider nicht hingekriegt... Wie macht man das?
Hier hast du eine massiv abgespeckte Version meines Files https://www.herber.de/bbs/user/101761.xlsm
, wo der Fehler ebenfalls eintritt.
Danke für deine super Hilfe!
Gruss,
Moe

Bild

Betrifft: AW: VBA Datenquelle Diagramm anpassen
von: Beverly
Geschrieben am: 24.11.2015 11:01:27
Hi Moe,
1. ergänze im ElseIf-Zweig noch .OLEFormat.Object

        ElseIf shaElement.Type = msoTextBox Then
            If InStr(shaElement.OLEFormat.Object.Formula, "Datencluster_Testszenarien") > 0  _
Then _
                shaElement.OLEFormat.Object.Formula = Application.Substitute( _
                shaElement.OLEFormat.Object.Formula, "Datencluster_Testszenarien", " _
Datencluster_GVZ")
        End If
2. du hast einige TextBoxen in den Gruppierungen, in denen als Formel steht =Datencluster_Testszenarien!#BEZUG! - das löst auch einen Fehler aus. Ob dieser Fehler auch in deine Originalmappe besteht, weiß ich nicht.
Das Shape erkennen kann man auf folgendem Weg: setze eine Überwachung auf shaElement und lasse den Code laufen. Kommt er zu dem Fehler, kannst du den Namen das Shapes im Überwachungsfenster ablesen: klicke das "+" an - da öffnet sich eine Art Browser und es werden alle Eigenschaften des überwachten Ausdrucks angezeigt, auch der Name. Das betrifft den ElseIf-Zweig. Im If-Zweig müsstest du shaElement.GroupItems(intZaehler) überwachen, da es ein bestimmtes Element der Gruppierung ist.
Wenn du den Namen des Elements kennst, dann drücke F10 und es werden alle Objekte aufgelistet. Klicke dort dann den entsprechenden Namen an und es wird in der Tabelle markiert.
Noch eine generelle Sache: du kannst ein (oder mehrere) Textfeld(er) auch direkt in ein Diagramm einfügen: markiere das Diagramm und füge das Textfeld ein - dann ist es mit dem Diagramm verbunden und du benötigst keine Gruppierung. Allerdimgs müsste man dann den Code für die Diagramme etwas erweitern und nicht nur die Titel bearbeiten.



 Bild

Beiträge aus den Excel-Beispielen zum Thema "VBA Datenquelle Diagramm anpassen"