Live-Forum - Die aktuellen Beiträge
Datum
Titel
20.04.2024 00:49:19
Excel Sheet in einen Excel Kalender einfügen
20.04.2024 00:08:11
In Ordner nur das Speichern von Dateien erlauben
19.04.2024 21:26:15
mehrfache Texte finden
19.04.2024 19:41:34
Bei VBA Ausführung Anzeige einfrieren
Du findest keine passende Antwort auf Deine Frage?
Stelle Deine Frage in unserem Live-Forum
Anzeige
Archiv - Navigation
1456to1460
Aktuelles Verzeichnis
Verzeichnis Index
Übersicht Verzeichnisse
Vorheriger Thread
Rückwärts Blättern
Nächster Thread
Vorwärts blättern
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Vorheriger Thread
Vorheriger Thread
Rückwärts Blättern
1456to1460
1456to1460
Aktuelles Verzeichnis
Verzeichnis Index
Verzeichnis Index
Übersicht Verzeichnisse
Nächster Thread
Nächster Thread
Vorwärts blättern
Inhaltsverzeichnis

VBA Datenquelle Diagramm anpassen

VBA Datenquelle Diagramm anpassen
19.11.2015 15:55:52
Moe
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

18
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: VBA Datenquelle Diagramm anpassen
19.11.2015 18:05:00
Beverly
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.

Anzeige
AW: VBA Datenquelle Diagramm anpassen
20.11.2015 14:20:48
Moe
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

AW: VBA Datenquelle Diagramm anpassen
20.11.2015 15:49:11
Beverly
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 &gt 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

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

AW: VBA Datenquelle Diagramm anpassen
20.11.2015 16:05:00
Moe
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...

AW: VBA Datenquelle Diagramm anpassen
20.11.2015 16:27:00
Beverly
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 ;-) )

Anzeige
AW: VBA Datenquelle Diagramm anpassen
23.11.2015 08:48:10
Moe
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

AW: VBA Datenquelle Diagramm anpassen
23.11.2015 10:29:28
Beverly
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.

Anzeige
AW: VBA Datenquelle Diagramm anpassen
23.11.2015 11:06:43
Moe
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

AW: VBA Datenquelle Diagramm anpassen
23.11.2015 11:21:49
Beverly
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.

Anzeige
AW: VBA Datenquelle Diagramm anpassen
23.11.2015 12:04:44
Moe
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

AW: VBA Datenquelle Diagramm anpassen
23.11.2015 13:08:22
Beverly
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

Anzeige
AW: VBA Datenquelle Diagramm anpassen
23.11.2015 14:12:59
Moe
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

AW: VBA Datenquelle Diagramm anpassen
23.11.2015 19:09:58
Beverly
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.

Anzeige
AW: VBA Datenquelle Diagramm anpassen
24.11.2015 08:00:15
Moe
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

AW: VBA Datenquelle Diagramm anpassen
24.11.2015 08:14:35
Beverly
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.

Anzeige
AW: VBA Datenquelle Diagramm anpassen
24.11.2015 09:12:41
Moe
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

AW: VBA Datenquelle Diagramm anpassen
24.11.2015 11:01:27
Beverly
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.

Anzeige

Links zu Excel-Dialogen

TextBox

Beliebteste Forumthreads (12 Monate)

Anzeige
Jetzt das Excel-Portal bewerten: Deine Stimme zählt!

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige