VBA Pivot erweitern, reduzieren per Butt

Forumthread: VBA Pivot erweitern, reduzieren per Butt

20.03.2021 18:43:40

MarC

Hallo zusammen,
ich habe mir zwei Buttons (Plus und Minus) erstellt, um alle meine PivotCharts gleichzeitig zu reduzieren oder zu erweitern. Das heißt ich kann von Jahr auf Quartal oder umgekehrt springen. Jetzt möchte ich noch eine Stufe weiter runter gehen auf die Monate und wieder zurück. Muss ich das über einen Merker machen der sich den Status merkt oder zwei zusätzliche Buttons erstellen?

Sub Diagramm_reduzieren()
With Worksheets("Pivot Board")
.PivotTables("PivotTable8").PivotFields("Ende Jahr").ShowDetail = False
.PivotTables("PivotTable9").PivotFields("Ende Jahr").ShowDetail = False
End With
End Sub
Sub Diagramm_erweitern()
With Worksheets("Pivot Board")
.PivotTables("PivotTable8").PivotFields("Ende Jahr").ShowDetail = True
.PivotTables("PivotTable9").PivotFields("Ende Jahr").ShowDetail = True
End With
End Sub

Beiträge zum Forumthread

Beiträge zu diesem Forumthread

Betreff

Datum

Anwender

AW: VBA Pivot erweitern, reduzieren per Butt

22.03.2021 14:26:38

fcs

Hallo Marc,
ich hatte einen Versuch gestartet, mit einem Status zu arbeiten, bin aber schon daran gescheitert den Status erweiter/reduziert für die Felder auf einfachem Wege zu ermitteln, bin aber daran gescheitert die Hilfe dazu bei Microsoft umzusetzen.
https://docs.microsoft.com/de-de/office/vba/api/excel.range.showdetail?f1url=%3FappId%3DDev11IDEF1%26l%3Dde-DE%26k%3Dk(vbaxl10.chm144196);k(TargetFrameworkMoniker-Office.Version%3Dv16)%26rd%3Dtrue
Deshalb mein Vorschlag: 2 Extra Buttons zum Erweitern bzw. Reduzieren.
Den Namen "Quartale" musst du ggf. anpassen.

Sub Diagramm_reduzierenQuartal()
With Worksheets("Pivot Board")
.PivotTables("PivotTable8").PivotFields("Quartale").ShowDetail = False
.PivotTables("PivotTable9").PivotFields("Quartale").ShowDetail = False
End With
End Sub
Sub Diagramm_erweiternQuartal()
With Worksheets("Pivot Board")
.PivotTables("PivotTable8").PivotFields("Quartale").ShowDetail = True
.PivotTables("PivotTable9").PivotFields("Quartale").ShowDetail = True
End With
End Sub

LG
Franz

AW: VBA Pivot erweitern, reduzieren per Butt

22.03.2021 15:43:27

MarC

Danke Franz für deine Unterstützung. Eigentlich wollte ich es vermeiden 4 Buttons zu erstellen, aber wenn es nicht anders geht muss das wohl so sein.
Ich dachte mir das man den Status der Detailtiefe also der drei Ebenen Jahr, Quartal, Monat ermitteln kann und je nachdem ob man den Plus oder Minus Button drückt geht man tiefer rein oder raus.
Hat vielleicht jemand anders da draußen eine Idee? Oder sollte ich hierfür eine Beispieldatei erstelln?
Gruß marC

AW: VBA Pivot erweitern, reduzieren per Butt

23.03.2021 00:18:49

fcs

Hallo Marc,
ich habe noch etwas getüftelt und eine Lösung gefunden.
Die Lösung ist aber sehr speziell für die jeweiligen Pivot-Tabellen. Falls du damit nicht zurechtkommst, dann lade bitte eine Beispieldatei hoch. Eine Beispieltabelle hilft fast immer bei einer Lösung.

Sub Diagramm_reduzieren()
Dim intStatus As Integer
With Worksheets("Pivot Board")
intStatus = fncStatusEnde_Jahr(.PivotTables("PivotTable8").PivotFields("Ende Jahr")) _
+ fncStatusQuartale(.PivotTables("PivotTable8").PivotFields("Quartale")) * 10
Select Case intStatus
Case 11 'Quartale und Monate sind eingeblendet
.PivotTables("PivotTable8").PivotFields("Quartale").ShowDetail = False
.PivotTables("PivotTable9").PivotFields("Quartale").ShowDetail = False
Case 21 'nur Quartale  sind eingeblendet
.PivotTables("PivotTable8").PivotFields("Ende Jahr").ShowDetail = False
.PivotTables("PivotTable9").PivotFields("Ende Jahr").ShowDetail = False
Case 2 'Quartale und Monate sind ausgeblendet
Case Else
End Select
End With
End Sub
Sub Diagramm_erweitern()
Dim intStatus As Integer
With Worksheets("Pivot Board")
intStatus = fncStatusEnde_Jahr(.PivotTables("PivotTable8").PivotFields("Ende Jahr")) _
+ fncStatusQuartale(.PivotTables("PivotTable8").PivotFields("Quartale")) * 10
Select Case intStatus
Case 11 'Quartale und Monate sind eingeblendet
Case 21 'nur Quartale  sind eingeblendet
.PivotTables("PivotTable8").PivotFields("Quartale").ShowDetail = True
.PivotTables("PivotTable9").PivotFields("Quartale").ShowDetail = True
Case 2 'Quartale und Monate sind ausgeblendet
.PivotTables("PivotTable8").PivotFields("Ende Jahr").ShowDetail = True
.PivotTables("PivotTable9").PivotFields("Ende Jahr").ShowDetail = True
Case Else
End Select
End With
End Sub
Function fncStatusQuartale(pvField As PivotField) As Integer
Dim bolStatus As Boolean
Dim strItem As String
Dim rngZelle As Range
strItem = "Qrtl"
bolStatus = False
For Each rngZelle In pvField.DataRange.Cells
If Left(rngZelle.Text, 4) = strItem Then
bolStatus = rngZelle.ShowDetail
If bolStatus = True Then
fncStatusQuartale = 1 'Monate werden angezeigt
Else
fncStatusQuartale = 2 'Monate sind ausgeblendet
End If
Exit For
End If
Next
End Function
Function fncStatusEnde_Jahr(pvField As PivotField) As Integer
Dim bolStatus As Boolean
Dim strItem As String
Dim rngZelle As Range
strItem = pvField.PivotItems(1).Name
If Not IsNumeric(Left(strItem, 1)) Then
strItem = Mid(pvField.PivotItems(1).Name, 2)
End If
strItem = CDate(strItem)
strItem = Right(strItem, 4)
For Each rngZelle In pvField.DataRange.Cells
If rngZelle.Text = strItem Then
bolStatus = rngZelle.ShowDetail
If bolStatus = True Then
fncStatusEnde_Jahr = 1 'Quartale werden angezeigt
Else
fncStatusEnde_Jahr = 2 'Quartale sind ausgeblendet
End If
Exit For
End If
Next
End Function

LG
Franz

Infobox / Tutorial

VBA für Pivot-Tabellen: Plus- und Minus-Buttons zum Erweitern und Reduzieren

Schritt-für-Schritt-Anleitung
Häufige Fehler und Lösungen
Alternative Methoden
Praktische Beispiele
Tipps für Profis
FAQ: Häufige Fragen

Schritt-für-Schritt-Anleitung

Buttons erstellen: Erstelle zwei Schaltflächen in deinem Excel-Dokument, einen für das Erweitern und einen für das Reduzieren der Pivot-Daten.
VBA-Editor öffnen: Drücke ALT + F11, um den VBA-Editor zu öffnen.
Modul hinzufügen: Füge ein neues Modul hinzu, indem du im Menü auf Einfügen > Modul klickst.
Code einfügen: Füge den folgenden Code in das Modul ein, um die Pivot-Tabelle zu erweitern und zu reduzieren:

Sub Diagramm_reduzieren()
    With Worksheets("Pivot Board")
        .PivotTables("PivotTable8").PivotFields("Ende Jahr").ShowDetail = False
        .PivotTables("PivotTable9").PivotFields("Ende Jahr").ShowDetail = False
    End With
End Sub

Sub Diagramm_erweitern()
    With Worksheets("Pivot Board")
        .PivotTables("PivotTable8").PivotFields("Ende Jahr").ShowDetail = True
        .PivotTables("PivotTable9").PivotFields("Ende Jahr").ShowDetail = True
    End With
End Sub

Buttons zuweisen: Weisen den Schaltflächen die entsprechenden Makros zu, indem du mit der rechten Maustaste auf die Schaltfläche klickst und Makro zuweisen wählst.

Häufige Fehler und Lösungen

Fehler 1: Pivot-Tabelle wird nicht aktualisiert.
- Lösung: Stelle sicher, dass die Pivot-Tabelle auf die korrekten Datenquellen verweist. Aktualisiere die Tabelle über Daten > Aktualisieren.
Fehler 2: Buttons funktionieren nicht.
- Lösung: Überprüfe, ob die Makros aktiviert sind und ob die Schaltflächen den richtigen Makros zugewiesen sind.

Alternative Methoden

Eine alternative Methode zur Steuerung der Detailansicht in Pivot-Tabellen ist die Verwendung von Excel-Datenfiltern. Du kannst auch Slicer einschalten, um die Daten interaktiv zu filtern. Dies kann die Benutzerfreundlichkeit erhöhen, da Slicer oft intuitiver sind als das Erweitern oder Reduzieren von Pivot-Tabellen.

Praktische Beispiele

Einfaches Beispiel: Du hast eine Pivot-Tabelle, die Verkäufe nach Jahr und Quartal anzeigt. Mit dem Plus-Button kannst du die Quartale anzeigen lassen, während der Minus-Button die Ansicht auf Jahre reduziert.
Erweiterte Nutzung: Du kannst den Code anpassen, um auch Monate einzuschließen. Hier ein Beispiel, das die Monate ebenfalls berücksichtigt:

Sub Diagramm_erweitern()
    Dim intStatus As Integer
    With Worksheets("Pivot Board")
        intStatus = fncStatusEnde_Jahr(.PivotTables("PivotTable8").PivotFields("Ende Jahr")) _
                    + fncStatusQuartale(.PivotTables("PivotTable8").PivotFields("Quartale")) * 10
        Select Case intStatus
            Case 21 'nur Quartale sind eingeblendet
                .PivotTables("PivotTable8").PivotFields("Quartale").ShowDetail = True
                .PivotTables("PivotTable9").PivotFields("Quartale").ShowDetail = True
            Case Else
                .PivotTables("PivotTable8").PivotFields("Ende Jahr").ShowDetail = True
                .PivotTables("PivotTable9").PivotFields("Ende Jahr").ShowDetail = True
        End Select
    End With
End Sub

Tipps für Profis

Nutze Statusvariablen für einen dynamischen Wechsel zwischen verschiedenen Detailstufen.
Halte deinen VBA-Code modular, um die Wartung zu erleichtern.
Experimentiere mit ShowDetail-Funktionen, um gezielt bestimmte Daten anzuzeigen oder auszublenden.

FAQ: Häufige Fragen

1. Wie kann ich die Pivot-Tabelle automatisch aktualisieren?
Du kannst die Pivot-Tabelle automatisch aktualisieren, indem du das Workbook_Open-Ereignis im VBA-Editor nutzt, um das Makro beim Öffnen der Datei auszuführen.

2. Kann ich die Buttons auch anders benennen?
Ja, du kannst die Schaltflächen nach Belieben umbenennen, um ihre Funktion klarer zu machen, z.B. "Details anzeigen" und "Details ausblenden".

3. Funktionieren diese Codes in jeder Excel-Version?
Die oben genannten Codes sollten in den meisten modernen Excel-Versionen funktionieren, die VBA unterstützen. Achte darauf, dass du eine kompatible Version verwendest.

Beliebteste Forumthreads (12 Monate)

VBA Pivot erweitern, reduzieren per Butt

Forumthread: VBA Pivot erweitern, reduzieren per Butt

3
Beiträge zum Forumthread

Beiträge zu diesem Forumthread

Forumthreads zu verwandten Themen

Infobox / Tutorial

VBA für Pivot-Tabellen: Plus- und Minus-Buttons zum Erweitern und Reduzieren

Schritt-für-Schritt-Anleitung

Häufige Fehler und Lösungen

Alternative Methoden

Praktische Beispiele

Tipps für Profis

FAQ: Häufige Fragen

Beliebteste Forumthreads (12 Monate)

VBA Pivot erweitern, reduzieren per Butt

Forumthread: VBA Pivot erweitern, reduzieren per Butt

3 Beiträge zum Forumthread Beiträge zu diesem Forumthread

Forumthreads zu verwandten Themen

Infobox / Tutorial

VBA für Pivot-Tabellen: Plus- und Minus-Buttons zum Erweitern und Reduzieren

Schritt-für-Schritt-Anleitung

Häufige Fehler und Lösungen

Alternative Methoden

Praktische Beispiele

Tipps für Profis

FAQ: Häufige Fragen

Beliebteste Forumthreads (12 Monate)

3
Beiträge zum Forumthread

Beiträge zu diesem Forumthread