Live-Forum - Die aktuellen Beiträge
Anzeige
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Inhaltsverzeichnis

Pivot-Grundeinstellung über VBA

Pivot-Grundeinstellung über VBA
15.04.2014 07:13:16
Werner
Guten Tag,
ich versuche mich derzeit an Möglichkeiten einer PivotTable. In der angehängten Mustermappe habe ich Einstellungen vorgenommen, die ich gerne über VBA als Grundeinstellung für die Abfrage vornehmen würde. Es tauchen dabei für mich Unwägbarkeiten auf, die ich in der beigefügten Mustermappe beschrieben habe.Ziel ist, wenn ich andere Einstellungen und Abfragen tätige, auf Schaltfläche immer wieder die Grundabfrage,-einstellung zu erreichen. Den bisherigen Code habe ich mit dem Makrorekorder aufgenommen. Ich bedanke mich schon jetzt für die Rückmeldungen.
Mit frdl. Grüßen
Werner
https://www.herber.de/bbs/user/90166.xlsm
P.S. in der geänderten Mustermappe sind etliche Spalten nicht logisch befüllt, da nicht relevant.

12
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: Pivot-Grundeinstellung über VBA
15.04.2014 14:23:58
fcs
Hallo Werner,
in deiner Datei hab ich mal die Makros entsprechend angepasst/ergänzt.
Das von dir aufgezeichnete Makro erstellt im aktiven Tabellenblatt eine Abfrage inklusive Pivotbericht.
Das ist aber "nur" bei der erstmaligen Erstellung des Pivotberichtes erforderlich.
Außerdem sollte dann eine vorhandene Abfrage/Pivotbericht gelöscht werden, bevor die neue erstellt wird.
Für die Grundeinstellungen muss nur der vorhandene Bericht neu aufgebaut werden.
Gruß
Franz
https://www.herber.de/bbs/user/90175.xlsm

Anzeige
AW: Pivot-Grundeinstellung über VBA
16.04.2014 06:10:24
Werner
Guten Tag, Franz.
herzlichen Dank für die viele Arbeit, die Du geleistet hast. Die Tabelle und die Makros sind genau so, wie ich es mir vorgestellt hatte. Ich würde nun gerne am Wochenende versuchen, die Makros entsprechend anzupassen, um sie dabei auch verstehen zu lernen. Werde mich auf jeden Fall erneut melden und hoffe, dass ich mich auch bei weiteren Fragen noch wieder erneut melden darf. Die Beschreibungen von Dir klingen sehr plausibel und verständlich, wäre selbst niemals darauf gekommen und hätte die Mappe auch nicht so hinbekommen. Nochmals zunächst vielen Dank, melde mich spätestens nächste Woche erneut. Schon jetzt somit Frohe und geruhsame Osterfeiertage.
Mit frdl. Grüßen
Werner

Anzeige
AW: Pivot-Grundeinstellung über VBA
17.04.2014 19:25:30
Werner
Guten Tag, Franz,
ich habe mich (mit meinen wenigen Möglichkeiten) intensiv mit Deiner Mustermappe befassen können und schon viele Dinge anpassen/klären können. Hätte aber dennoch Verständnisfragen: Wo würde ich bezüglich der Formatierung ansetzen können? - Gibt es Möglichkeiten, bestimmte Spalten dynamisch zu gestalten oder zumindest die Breite zu verändern? Wann kommen die Makros 6 bis 9 zum Tragen bzw. an welcher Stelle würde ich diese einsetzen? Ich werde mich am Wochenende noch weiter mit der Mappe befassen und Dich weiterhin a.d. Laufenden halten bzw. noch weitere Fragen stellen. Zunächst nochmals vielen Dank und ein schönes Osterwochenende.
Mit frdl. Grüßen
Werner

Anzeige
AW: Pivot-Grundeinstellung über VBA
18.04.2014 07:55:55
fcs
Hallo Werner,
die Makros 6 bis 9 sind mit dem Makrorekorder aufgezeichnete Code-Schnippsel, die ich dann in die eigentlichen Makros eingebaut hab.
Aufgezeichnete Makros sind oft hilfreich, wenn man die korrekte Syntax oder einen VBA-Befehl nicht kennt. Es wird zwar auch jede Menge an Code-Müll oder sub-optimaler Code (Scroll-Befehle, .Select, .Activate und Selection...) mit aufgezeichnet, aber es ist oft einfacher solche Code-Schnippsel zu optimieren als alles mühselig zusammen zu suchen.
Ich hatte vergessen, diese Makros vor dem Hochladen zu löschen; das kannst du jetzt nachholen.
Einige Formatierungen hab ich hier schon eingebaut:
    'bedingte Formatierung erstellen für Zellen mit 0, 1 oder 2
Set pvField = pvTab.ColumnFields("Status Entscheidung")
Call bedingtFormatieren(rng:=pvField.DataRange)
'Spalte/Zeile für Gesamtwerte (J:K)
Spalte = pvField.DataRange.Column + pvField.DataRange.Columns.Count
Zeile = pvField.LabelRange.Row + 1
'Formatierungen
With ActiveSheet
pvTab.PivotFields("Produkt").LabelRange.EntireColumn.ColumnWidth = 14
pvTab.PivotFields("Team").LabelRange.EntireColumn.ColumnWidth = 14
pvField.DataRange.EntireColumn.ColumnWidth = 16
With .Range(.Cells(Zeile, Spalte), .Cells(Zeile, Spalte + 1))
.EntireColumn.ColumnWidth = 19
.WrapText = True
End With
End With

Hier kannst du z.B. Werte für die Spalten-Breiten anpassen.
Für die Zahlenformate im Datenbereich ist hier ein Beispiel:
    With pvTab.DataFields("% von Status Entscheidung")
.Calculation = xlPercentOfRow
.NumberFormat = "0%"
End With
Hier musst du immer erst das Feld anlegen. Danach sind dann Änderungen an den Einstellungen und Zahlenformaten möglich.
Gruß
Franz

Anzeige
Überschriften verändern sich
19.04.2014 11:57:57
Werner
Guten Morgen, Franz,
zunächst vielen Dank für Deine erneute Rückmeldung und die weiteren Hinweise zum Handling. Beim weiteren Testen fiel mir auf, dass sich die Überschriften in der Abfrage "Vertrag ja", "Vertrag nein" und "unentschlossen" nach dem erneuten Aufbau über Schaltfläche Grundeinstellungen verändern. Habe hierzu einmal zwei Screenshots angefügt. Hättest Du da evtl. eine Idee, was da verändert werden könnte?
Mit frdl. Grüßen
Werner
Userbild
Userbild

Anzeige
AW: Überschriften verändern sich
20.04.2014 10:47:44
fcs
Hallo Werner,
dieses Phänomen hab ich bisher nicht beobachtet. Selbst nach massiven Veränderungen am Pivotbericht (Filter, zusätzliche Felder, weniger Felder) wurden die Grundeinstellungen wieder korrekt hergestellt.
In soweit kann ich dir da nicht weiter helfen.
Prüfe mal, ob in den Zellen, die 0, 1 oder 2 anzeigen statt der Texte, bedingte Formatierungen vorhanden sind.
Gruß
Franz

AW: Überschriften verändern sich
20.04.2014 11:04:55
Werner
Guten Tag, Franz,
ich habe eben noch einmal probelhalber die von Dir upgeloadete Datei heruntergeladen und erneut probiert. Das Problem taucht bereits hierin auf, ohne dass ich Veränderungen vorgenommen habe. Ich habe dabei die Datei auch auf unterschiedliche Rechner (privat und dienstlich) probiert. Es taucht nach Betätigen der Grundeinstellungen das gleiche Bild auf. Bedingte Formatiertung kann ich nicht entdecken, außer, dass sich hierzu im Modul2 der nachfolgende Code in der Mappe befindet.
Mit frdl. Grüßen
Werner
  • 
    Sub bedingtFormatieren(rng As Range)
    ' bedingtFormatieren Makro
    Dim objFC As FormatCondition, strZelle As String
    'Addresse der 1. Zelle des Bereichs ohne absolute Bezüge
    strZelle = rng.Range("A1").Address(False, False, ReferenceStyle:=xlA1)
    rng.FormatConditions.Add Type:=xlExpression, _
    Formula1:="=(" & strZelle & """"")*(" & strZelle & "=0)"
    Set objFC = rng.FormatConditions(1)
    objFC.NumberFormat = ";;""Vertrag liegt vor"";"
    rng.FormatConditions.Add Type:=xlExpression, _
    Formula1:="=(" & strZelle & """"")*(" & strZelle & "=1)"
    Set objFC = rng.FormatConditions(2)
    objFC.NumberFormat = """kein Vertrag"";;;"
    rng.FormatConditions.Add Type:=xlExpression, _
    Formula1:="=(" & strZelle & """"")*(" & strZelle & "=2)"
    Set objFC = rng.FormatConditions(3)
    objFC.NumberFormat = """unentschlossen"";;;"
    End Sub
    

  • Anzeige
    AW: Überschriften verändern sich
    20.04.2014 11:34:25
    Werner
    Hallo Franz,
    hatte mich noch einmal mit der bedingten Formatierung befasst und korrigiere mich. Unter bedingte Formatierung ist doch eine Formel enthalten. Die Häkchen "Anhalten" sind gesetzt. Entferne ich die Häkchen und betätige dann die Schaltfläche, wird alles wieder entsprechend mit den Überschriften aufgebaut. Rufe ich die Mappe nach Speicherung wieder auf, sind zwar die Häkchen wieder da, nun werden aber die Überschriften sauber übernommen. Problem scheint damit gelöst. Herzlichen Dank für Deinen Hinweis. Werde noch weiter anpassen und versuchen. Melde mich auf jeden Fall noch wieder, um den Sachstand mitzuteilen.
    Mit frdl. Grüßen
    Werner

    Anzeige
    "spielt trotzdem noch verrückt"
    20.04.2014 11:43:52
    Werner
    Hallo Franz,
    hatte soeben noch angenommen, den Fehler gefunden bzw. das Problem gelöst zu haben. Eine Veränderung und schon sind die Überschriften wieder durcheinander. Wenn ich dann die Häkchen entferne und die Schaltfläche wieder betätige (Grundeinstellungen) erscheinen erst die richtigen Überschriften wieder. Liegt die Ursache tatsächlich in den Häkchen?
    Mit frdl. Grüßen
    Werner

    AW: "spielt trotzdem noch verrückt"
    20.04.2014 15:33:31
    fcs
    Hallo Werner,
    ich hab jetzt keinen Schimmer, welche Einstellung hier den "Anhaltenstatus" der bedingten Formatierungen beeinflußt und warum es bei dir nicht funktioniert.
    Eigentlich sollte das nur für die Priorität der bedingten Formatierungen eine Rolle spielen, wenn mehrere Bedingungen erfüllt sein können.
    Bei mir spielt die Einstellung keine Rolle, die Textanzeige ist immer komplett.
    Ich hab das Makro jetz nochmals erweitert, so dass alle bedingten Formatierungen im Bereich zu Beginn gelöscht werden. Die Häkchen für Anhalten werden nicht gesetzt.
    Gruß
    Franz
    Sub bedingtFormatieren(rng As Range)
    ' bedingtFormatieren Makro
    Dim objFC As FormatCondition, strZelle As String, bolAnhalten As Boolean
    rng.FormatConditions.Delete 'alle vorhanden bedingten Formatierungen löschen
    bolAnhalten = False 'Status für Anhalten in bedingten Formatierungen
    'Addresse der 1. Zelle des Bereichs ohne absolute Bezüge
    strZelle = rng.Range("A1").Address(False, False, ReferenceStyle:=xlA1)
    rng.FormatConditions.Add Type:=xlExpression, _
    Formula1:="=(" & strZelle & """"")*(" & strZelle & "=0)"
    Set objFC = rng.FormatConditions(1)
    objFC.NumberFormat = ";;""Vertrag liegt vor"";"
    objFC.StopIfTrue = bolAnhalten
    rng.FormatConditions.Add Type:=xlExpression, _
    Formula1:="=(" & strZelle & """"")*(" & strZelle & "=1)"
    Set objFC = rng.FormatConditions(2)
    objFC.NumberFormat = """kein Vertrag"";;;"
    objFC.StopIfTrue = bolAnhalten
    rng.FormatConditions.Add Type:=xlExpression, _
    Formula1:="=(" & strZelle & """"")*(" & strZelle & "=2)"
    Set objFC = rng.FormatConditions(3)
    objFC.NumberFormat = """unentschlossen"";;;"
    objFC.StopIfTrue = bolAnhalten
    End Sub
    

    Anzeige
    Danke - funktioniert nun soweit
    21.04.2014 07:25:04
    Werner
    Guten Morgen, Franz,
    ich habe gestern noch weiter probiert und getestet und dabei auch den neuen Code eingesetzt. Ich konnte irgendwann klären, dass die Überschriften nur dann sauber übernommen werden, wenn die erste Zelle -im Beispiel D12-, in der sich der erste Bezeichner befindet (Vertrag liegt vor), markiert ist. Habe den Code nun so ergänzt, dass diese Zelle automatisch vorab markiert wird und dann Dein Code erst startet. Seitdem werden die Überschriften immer wunderbar eingebaut. Herzlichen Dank für Deine jeweils erneuten Rückeldungen und Geduld mit mir. Werde Morgen die Mappe auf einem Dienstrechner testen und mich auf jeden Fall noch kurz wieder melden.
    Mit frdl. Grüßen
    Werner

    Anzeige
    andere Frage - Datumsformat in Spalte R und S
    21.04.2014 13:53:33
    Werner
    Hallo Franz,
    ich versuche noch weiter, die Pivotabelle anzupassen/einzurichten und möchte nun als nächstes die jeweiligen Datumseintragungen aus Spalte R und S, die hierin als Monat und Jahr angezeigt werden, "installieren". Die Umsetzung im Code konntest Du mir sehr gut erklären und funktioniert auch soweit. Was muss ich allerdings ergänzen/verändern, damit das Datum auch weiterhin im Format Monat/Jahr angezeigt wird und nicht in der Folge als kpl. Datum?
    Mit frdl.Grüßen
    Werner

    302 Forumthreads zu ähnlichen Themen

    Anzeige
    Anzeige
    Anzeige

    Links zu Excel-Dialogen

    Beliebteste Forumthreads (12 Monate)

    Anzeige

    Beliebteste Forumthreads (12 Monate)

    Anzeige
    Anzeige
    Anzeige