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
    6. Application.EnableEvents = False
    7. Application.ScreenUpdating = False
    9. If ActiveSheet.Name = Me.Name Then
    10. For Each pf In Target.PageFields
    11. If pf.CurrentPage = "(All)" Then
    12. Application.Undo
    13. MsgBox "Please select a single date."
    14. End If
    15. Next pf
    16. End If
    18. exit_Handler:
    19. Set pf = Nothing
    20. Application.EnableEvents = True
    21. Application.ScreenUpdating = True
    22. 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, 117 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
    3. Private Sub Worksheet_PivotTableUpdate _
    4. (ByVal Target As PivotTable)
    6. Dim pf As PivotField
    7. Dim pfpos As Integer
    8. On Error GoTo exit_Handler
    10. Application.EnableEvents = False
    11. Application.ScreenUpdating = False
    13. For Each pf In Target.PageFields
    14. pf.EnableMultiplePageItems = True
    15. If pf.CurrentPage = "(All)" Then
    16. Application.Undo
    17. MsgBox "Selection (ALL) ist not allowed."
    18. End If
    19. Next pf
    21. exit_Handler:
    23. Set pf = Nothing
    24. Application.EnableEvents = True
    25. Application.ScreenUpdating = True
    27. 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
    3. Private Sub Worksheet_PivotTableUpdate _
    4. (ByVal Target As PivotTable)
    6. Dim pf As PivotField
    8. On Error GoTo exit_Handler
    10. Application.EnableEvents = False
    11. Application.ScreenUpdating = False
    13. For Each pf In Target.PageFields
    14. pf.EnableMultiplePageItems = True
    15. If pf.Name = "Ort" Then
    16. If pf.CurrentPage = "(All)" Then
    17. Application.Undo
    18. MsgBox "Selection (ALL) ist not allowed."
    19. End If
    20. End If
    21. Next pf
    23. exit_Handler:
    25. Set pf = Nothing
    26. Application.EnableEvents = True
    27. Application.ScreenUpdating = True
    29. 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
    16. End If
    17. 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
    3. Private Sub Worksheet_PivotTableUpdate _
    4. (ByVal Target As PivotTable)
    6. Dim pf As PivotField
    7. Dim pi As PivotItem
    8. Dim blnNichtVorhanden As Boolean
    10. On Error GoTo exit_Handler
    12. Application.EnableEvents = False
    13. Application.ScreenUpdating = False
    15. For Each pf In Target.PageFields
    17. blnNichtVorhanden = False
    19. If pf.Name = "Ort" Then
    20. For Each pi In pf.PivotItems
    21. If pi.Visible = False Then
    22. blnNichtVorhanden = True
    23. Exit For
    24. End If
    25. Next
    26. If blnNichtVorhanden = False Then
    27. Application.Undo
    28. MsgBox "Selection (ALL) ist not allowed."
    29. End If
    30. End If
    32. If pf.Name = "Kost" Then
    33. For Each pi In pf.PivotItems
    34. If pi.Visible = False Then
    35. blnNichtVorhanden = True
    36. Exit For
    37. End If
    38. Next
    39. If blnNichtVorhanden = False Then
    40. Application.Undo
    41. MsgBox "Selection (ALL) ist not allowed."
    42. End If
    43. End If
    45. Next pf
    47. exit_Handler:
    49. Set pf = Nothing
    50. Application.EnableEvents = True
    51. Application.ScreenUpdating = True
    53. 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. ...
    3. Weiter:
    4. Set pf = Nothing
    5. Application.EnableEvents = True
    6. Application.ScreenUpdating = True
    7. Exit Sub
    8. exit_Handler:
    9. Msgbox Err.Number &" "& Err.Description
    10. 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
    10. '-------------- NEU
    11. If pf.Name = ActiveSheet.PivotTables("PivotTable1").PivotFields( _
    12. "[Report Currency].[Report Currency Id].[Report Currency Id]") Then
    14. For Each pi In pf.PivotItems
    15. If pi.Visible = False Then
    16. blnNichtVorhanden = True
    17. Exit For
    18. End If
    19. 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
    3. Private Sub Worksheet_PivotTableUpdate _
    4. (ByVal Target As PivotTable)
    6. Dim pf As PivotField
    7. Dim pi As PivotItem
    8. Dim blnNichtVorhanden As Boolean
    10. On Error GoTo exit_Handler
    12. Application.EnableEvents = False
    13. Application.ScreenUpdating = False
    15. For Each pf In Target.PageFields
    17. If pf.Name = ActiveSheet.PivotTables("PivotTable1").PivotFields( _
    18. "[Report Currency].[Report Currency Id].[Report Currency Id]") Then
    19. If pf.DataRange.Text = "All" Then
    20. MsgBox "Selection (ALL) is not allowed."
    21. Application.Undo
    22. Exit For
    23. End If
    24. End If
    26. If pf.Name = ActiveSheet.PivotTables("PivotTable1").PivotFields( _
    27. "[Cost].[Cost Center Id].[Cost Center Id]") Then
    28. If pf.DataRange.Text = "All" Then
    29. MsgBox "Selection (ALL) is not allowed."
    30. Application.Undo
    31. Exit For
    32. End If
    33. End If
    35. Next pf
    37. exit_Handler:
    39. Set pf = Nothing
    40. Application.EnableEvents = True
    41. Application.ScreenUpdating = True
    43. End Sub


    Viele Grüsse
    Manfred