Pivotfilter Auswahl von (ALLE) blockieren

  • Excel

Es gibt 11 Antworten in diesem Thema. Der letzte Beitrag () ist von empfl.

    Pivotfilter Auswahl von (ALLE) blockieren

    Hallo an alle,

    bin im Bezug auf die Codierung von Makro für Pivot-Tabelle ein absoluter Newbie.

    Ich möchte gerne mit einem Makro, bei einer Pivot-Tabelle die Auswahl von (ALLE)
    mit einem Makro verhindern.

    Ich habe dazu schon den folgenden Code gefunden.

    Visual Basic-Quellcode

    1. Private Sub Worksheet_PivotTableUpdate _
    2. (ByVal Target As PivotTable)
    3. Dim pf As PivotField
    4. On Error GoTo exit_Handler
    5. Application.EnableEvents = False
    6. Application.ScreenUpdating = False
    7. If ActiveSheet.Name = Me.Name Then
    8. For Each pf In Target.PageFields
    9. If pf.CurrentPage = "(All)" Then
    10. Application.Undo
    11. MsgBox "Please select a single date."
    12. End If
    13. Next pf
    14. End If
    15. exit_Handler:
    16. Set pf = Nothing
    17. Application.EnableEvents = True
    18. Application.ScreenUpdating = True
    19. End Sub


    Allerdings funktioniert dieses Makro zwar, aber ein bisschen komisch.

    Es sollte auch so sein, dass nur bestimmte Filter auf den Eintrag "(Alle)" geprüft werden
    sollen und wenn diese dann geändert wurden soll wieder der alte Eintrag gelten.

    Viele liebe Grüße
    Manfred

    Code-Tags eingefügt. (Du hast den Inline-Tag verwendet, der funktioniert aber nur für Einzeiler ;) ) ~Thunderbolt
    Dateien
    • makro test.xlsm

      (23,63 kB, 78 mal heruntergeladen, zuletzt: )

    Dieser Beitrag wurde bereits 1 mal editiert, zuletzt von „Thunderbolt“ ()

    Aus Deiner Msgbox "Please select a single date." entnehme ich, dass der Anwender nur einen Wert verwenden darf.
    Warum läßt Du dann dies zu?

    Im Code kannst Du dies mit EnableMultiplePageItems steuern

    Visual Basic-Quellcode

    1. For Each pf In Target.PageFields
    2. pf.EnableMultiplePageItems = False
    3. If pf.CurrentPage = "(All)" Then
    4. Application.Undo
    5. MsgBox "Please select a single date."
    6. End If
    7. Next pf


    Die Zeile "If ActiveSheet.Name = Me.Name Then" wird nicht gebraucht, da Dein Sub sowieso nur für das aktuelle Tabellenblatt gilt.

    Denn der Code nur für einzelne Filter gelten soll, muss Du diese mit ihrem Namen auch ansprechen.

    Am Ende noch ein Tipp. Schalte in den Optionen von VBA die variablen-Deklaration als Standard ein.
    NB. Es ist doch schön, wenn man lesbare Namen vergibt. Siehe auch [VB.NET] Beispiele für guten und schlechten Code (Stil).
    Hallo INOPIAE,

    vielen Dank für deine Antwort.

    Ich habe da den falsche Text für die MsgBox gepostet.

    Ich möchte dass die Auswahl "(ALL)" nur für einzelne Filter unterbunden werden soll.

    In der Beispieldatei z.Bsp. nur für 'Kost' und 'Ort'; für Einwohner soll die Auswahl "(ALL)" möglich sein.
    Jetzt wird dort mit dem Makro für alle Filter unterbunden.

    Quellcode

    1. Option Explicit
    2. Private Sub Worksheet_PivotTableUpdate _
    3. (ByVal Target As PivotTable)
    4. Dim pf As PivotField
    5. Dim pfpos As Integer
    6. On Error GoTo exit_Handler
    7. Application.EnableEvents = False
    8. Application.ScreenUpdating = False
    9. For Each pf In Target.PageFields
    10. pf.EnableMultiplePageItems = True
    11. If pf.CurrentPage = "(All)" Then
    12. Application.Undo
    13. MsgBox "Selection (ALL) ist not allowed."
    14. End If
    15. Next pf
    16. exit_Handler:
    17. Set pf = Nothing
    18. Application.EnableEvents = True
    19. Application.ScreenUpdating = True
    20. End Sub


    Wie spreche ich die einzelnen Filter an ?

    Viele liebe Grüße
    Manfred
    Dateien
    Hallo Manfred,

    in der Schleife einfach die Felder abfragen:

    Visual Basic-Quellcode

    1. For Each pf In Target.PageFields
    2. pf.EnableMultiplePageItems = True
    3. If pf.Name="XX" then
    4. If pf.CurrentPage = "(All)" Then
    5. Application.Undo
    6. MsgBox "Selection (ALL) ist not allowed."
    7. End If
    8. End If
    9. Next pf


    Oder alternativ zum If ein Select Case.
    NB. Es ist doch schön, wenn man lesbare Namen vergibt. Siehe auch [VB.NET] Beispiele für guten und schlechten Code (Stil).
    Hallo INOPIAE, hallo an alle,

    vielen Dank für deine Antwort INOPIAE.

    Ich habe die Änderung eingebaut, das Makro funktioniert aber noch nicht so wie gewollt.
    Es wird im Prinzip jede Veränderung an den Filtern blockiert und wieder auf die Ursprungsauswahl
    zurück geändert.
    Es soll aber, wenn möglich nur die Änderung von bestimmten Filtern auf "(ALL)" verhindert werden.

    Im Code z. Bsp. bei Filter Ort soll Auswahl 'ALLE' nicht möglich sein. Bei Kost bzw. Einwohner soll eine Änderung der Filtereinstellungen bzw. auch die Auswahl "(ALL)" möglich sein.

    Visual Basic-Quellcode

    1. Option Explicit
    2. Private Sub Worksheet_PivotTableUpdate _
    3. (ByVal Target As PivotTable)
    4. Dim pf As PivotField
    5. On Error GoTo exit_Handler
    6. Application.EnableEvents = False
    7. Application.ScreenUpdating = False
    8. For Each pf In Target.PageFields
    9. pf.EnableMultiplePageItems = True
    10. If pf.Name = "Ort" Then
    11. If pf.CurrentPage = "(All)" Then
    12. Application.Undo
    13. MsgBox "Selection (ALL) ist not allowed."
    14. End If
    15. End If
    16. Next pf
    17. exit_Handler:
    18. Set pf = Nothing
    19. Application.EnableEvents = True
    20. Application.ScreenUpdating = True
    21. End Sub


    Viele liebe Grüße
    Manfred
    Dateien
    Hallo Manfred,

    probiere mal dies:

    Visual Basic-Quellcode

    1. For Each pf In Target.PageFields
    2. If pf.Name = "Ort" Then
    3. Dim pi As PivotItem
    4. Dim blnNichtVorhanden As Boolean
    5. For Each pi In pf.PivotItems
    6. If pi.Visible = False Then
    7. blnNichtVorhanden = True
    8. Exit For
    9. End If
    10. Next
    11. If blnNichtVorhanden = False Then
    12. Application.Undo
    13. MsgBox "Selection (ALL) ist not allowed."
    14. End If
    15. End If
    16. Next pf


    Man kann m.E. nicht auf "(All)" prüfen, aber wenn alles visible ist dann sind alle ausgewählt.

    Die Zeile mit dem On Error solltest Du Dir auch nochmal überlegen. So kannst Du keinen Fehler erkennen.
    NB. Es ist doch schön, wenn man lesbare Namen vergibt. Siehe auch [VB.NET] Beispiele für guten und schlechten Code (Stil).
    Hallo INOPIAE,

    vielen Dank für Hilfe.

    Ich habe nun den Code von Dir eingetragen und dann noch erweitert auf die Abfrage von zwei Pivot-Filtern.
    Das hat dann am Anfang nicht funktioniert, bis ich dann gemerkt habe dass ich "blnNichtVorhanden" zuerst
    (wieder) auf "False" stellen muss. Seither funktioniert das Makro wie gewünscht.

    Das mit der Zeile 'On Error' versteh ich nicht. Ich hatte den ursprüngliche Code im Internet gefunden.
    Ich meine fast, dass ich das "On Error" und die Zeile 'exit Handler' und drei folgende ganz löschen kann.

    Was meinst du ?

    Herzlichen Dank nochmals für deine Unterstützung,
    viele Grüße
    und alles Gute

    Manfred

    Quellcode

    1. Option Explicit
    2. Private Sub Worksheet_PivotTableUpdate _
    3. (ByVal Target As PivotTable)
    4. Dim pf As PivotField
    5. Dim pi As PivotItem
    6. Dim blnNichtVorhanden As Boolean
    7. On Error GoTo exit_Handler
    8. Application.EnableEvents = False
    9. Application.ScreenUpdating = False
    10. For Each pf In Target.PageFields
    11. blnNichtVorhanden = False
    12. If pf.Name = "Ort" Then
    13. For Each pi In pf.PivotItems
    14. If pi.Visible = False Then
    15. blnNichtVorhanden = True
    16. Exit For
    17. End If
    18. Next
    19. If blnNichtVorhanden = False Then
    20. Application.Undo
    21. MsgBox "Selection (ALL) ist not allowed."
    22. End If
    23. End If
    24. If pf.Name = "Kost" Then
    25. For Each pi In pf.PivotItems
    26. If pi.Visible = False Then
    27. blnNichtVorhanden = True
    28. Exit For
    29. End If
    30. Next
    31. If blnNichtVorhanden = False Then
    32. Application.Undo
    33. MsgBox "Selection (ALL) ist not allowed."
    34. End If
    35. End If
    36. Next pf
    37. exit_Handler:
    38. Set pf = Nothing
    39. Application.EnableEvents = True
    40. Application.ScreenUpdating = True
    41. End Sub
    Dateien
    Hi,

    falls Du einen Error Handler benutzt, solltest Du in so bauen, dass Du Fehler, die Du nicht kennst mit bekommst.

    So wie Du in einsetzt, springst Du im Fehlerfall zum Ende und bekommst dies nicht mit.

    Besser wäre es so was zu bauen:

    Visual Basic-Quellcode

    1. ...
    2. Weiter:
    3. Set pf = Nothing
    4. Application.EnableEvents = True
    5. Application.ScreenUpdating = True
    6. Exit Sub
    7. exit_Handler:
    8. Msgbox Err.Number &" "& Err.Description
    9. Resume Weiter
    NB. Es ist doch schön, wenn man lesbare Namen vergibt. Siehe auch [VB.NET] Beispiele für guten und schlechten Code (Stil).
    Hallo INOPIAE,

    vielen Dank für deine Antwort (am Tag der deutschen Einheit ;) ).

    Ich meine dann wohl Power-Pivot. Wir haben die Daten auf einem Server liegen und wir rufen diese dann mit Power-Pivot ab.

    Ich habe den Code wie folgt angepasst.

    Brainfuck-Quellcode

    1. '-------------- ALT
    2. If pf.Name = "Ort" Then
    3. For Each pi In pf.PivotItems
    4. If pi.Visible = False Then
    5. blnNichtVorhanden = True
    6. Exit For
    7. End If
    8. Next
    9. '-------------- NEU
    10. If pf.Name = ActiveSheet.PivotTables("PivotTable1").PivotFields( _
    11. "[Report Currency].[Report Currency Id].[Report Currency Id]") Then
    12. For Each pi In pf.PivotItems
    13. If pi.Visible = False Then
    14. blnNichtVorhanden = True
    15. Exit For
    16. End If
    17. Next


    Der "alte" Code bezeiit sich auf die Beispiel-Datei, der "neue"-Code bezieht sich auf meine aktuelle Datei.

    Für "pf.Name" konnte ich den Code entsprechend anpassen, bei "pi" ist es mir noch nicht gelungen.

    Viele liebe Grüße
    Manfred

    Dieser Beitrag wurde bereits 1 mal editiert, zuletzt von „empfl“ ()

    Hallo INOPIAE,

    ich glaube nun dass ich, mit umfangreicher Internetrecherche nun die Lösung gefunden habe.
    Also in meiner Datei (Power-Pivot) funktioniert es.
    Wenn du dir vielleicht nochmal kritisch anschauen könntest wäre ich dir dankbar,

    Quellcode

    1. Option Explicit
    2. Private Sub Worksheet_PivotTableUpdate _
    3. (ByVal Target As PivotTable)
    4. Dim pf As PivotField
    5. Dim pi As PivotItem
    6. Dim blnNichtVorhanden As Boolean
    7. On Error GoTo exit_Handler
    8. Application.EnableEvents = False
    9. Application.ScreenUpdating = False
    10. For Each pf In Target.PageFields
    11. If pf.Name = ActiveSheet.PivotTables("PivotTable1").PivotFields( _
    12. "[Report Currency].[Report Currency Id].[Report Currency Id]") Then
    13. If pf.DataRange.Text = "All" Then
    14. MsgBox "Selection (ALL) is not allowed."
    15. Application.Undo
    16. Exit For
    17. End If
    18. End If
    19. If pf.Name = ActiveSheet.PivotTables("PivotTable1").PivotFields( _
    20. "[Cost].[Cost Center Id].[Cost Center Id]") Then
    21. If pf.DataRange.Text = "All" Then
    22. MsgBox "Selection (ALL) is not allowed."
    23. Application.Undo
    24. Exit For
    25. End If
    26. End If
    27. Next pf
    28. exit_Handler:
    29. Set pf = Nothing
    30. Application.EnableEvents = True
    31. Application.ScreenUpdating = True
    32. End Sub


    Viele Grüsse
    Manfred