Wie kann ich in Excel ein Makro ausführen, wenn ein Filter von einer Tabelle geändert wird?
2 Antworten
Beim Ändern des Filters wird ein Calculate ausgeführt. Eine Änderung des Filters könntest du feststellen, indem du dir immer die Anzahl dargestellter Zeilen (TEILERGEBNIS) in einer globalen Variablen merkst und mit dem aktuellen Stand vergleichst. Zur Absicherung kannst du weitere Kriterien festlegen, falls der Filter sich ändert, die Anzahl dargestellter Zeilen jedoch nicht. Schau dir mal die TEILERGEBNIS-Funktion an. In VBA heißt sie "Subtotal".
Danke, jedoch mache ich Makro-Programmierung normalerweise nicht. Daher würde ich mich sehr freuen, wenn ich ein kleines Code-Beispiel sehen könnte.
Ein erster Ansatz
Option Explicit
Public sKriterium1 As String
Private Sub Worksheet_Activate() sKriterium1 = SerialisiereKriterium() Range("M1").Formula = "=SUBTOTAL(103,D2:D100)" ' WorksheetFunction.Subtotal(102, Range("D2:D100")) End Sub
Private Sub Worksheet_Calculate() 'debug'Debug.Print SerialisiereKriterium() If sKriterium1 <> SerialisiereKriterium() Then MsgBox "Der Filter wurde geändert" sKriterium1 = SerialisiereKriterium() End If End Sub
Function SerialisiereKriterium() As String SerialisiereKriterium = "" If Tabelle2.AutoFilter.Filters.Item(1).On Then If Tabelle2.AutoFilter.Filters.Item(1).Count >= 1 Then _ SerialisiereKriterium = SerialisiereKriterium & ";" & _ Tabelle2.AutoFilter.Filters.Item(1).Criteria1 If Tabelle2.AutoFilter.Filters.Item(1).Count >= 2 Then _ SerialisiereKriterium = SerialisiereKriterium & ";" & _ Tabelle2.AutoFilter.Filters.Item(1).Criteria2 If Tabelle2.AutoFilter.Filters.Item(1).Count >= 3 Then _ SerialisiereKriterium = SerialisiereKriterium & ";" & _ Tabelle2.AutoFilter.Filters.Item(1).Criteria3 If Tabelle2.AutoFilter.Filters.Item(1).Count >= 4 Then _ SerialisiereKriterium = SerialisiereKriterium & ";" & _ Tabelle2.AutoFilter.Filters.Item(1).Criteria4 '...
End If End Function
Das Blöde ist, dass ich es nicht auf Anhieb geschafft habe, die Filterkriterien über eine Schleife zu durchlaufen. Normalerweise habe ich zu einem Count eine Auflistung. Es gibt aber keine Criteria-Auflistung. Excel fügt jedesmal eine weitere Eigenschaft Criteria mit fortlaufendem Index hinzu.
K. A., ob es da sowas wie Reflections gibt.
Ich brauche eher sowas wie das hier:
Private Sub Worksheet_Calculate()
If ActiveSheet.AutoFilter.Filters.Item(1).On Then
AttachLabelsToPoints
End If
End Sub
Blos wenn ich das so mache, wie ich es heir stehen habe, dann Fehler: Index außerhalb des gültigen Bereiches.
Wie behebe ich das?
Du kannst vor dem Befehl mit "Is Nothing" überprüfen, ob das Objekt ansprechbar ist oder mit "Count" die Anzahl an Elementen zählen.
Count gibt 0 aus. Aber ich habe doch Filter auf meinem Blatt.
Es kommt darauf an, was du zählst. Hast du eventuell die aktiven Filterkriterien gezählt? Wenn du "Alle anzeigen" aktiv hast, sind die Filterkriterien Criteria1..n logischer Weise nicht ansprechbar.
Das meine ich damit, dass Excel es schlecht gelöst hat, die Filter als Eigenschaften dynamisch zu ergänzen.
Ich habe Filter die auf alle anzeigen sind, ich habe aber uach welche die nur Zeilen mit Wert über 20 Filtern. Ich habe nur den Code:
If ActiveSheet.AutoFilter.Filters.Item(1).On Then
Sonst nichts, weil ich die anderen Funktionen nicht brauche. Ich möchte einfach eine Funktion ausführen wenn ein Filter angesprochen wird/geändert wird.
"Filters" sind sozusagen alle Filterpfeile, wenn ich das richtig verstanden habe, also alle Spalten, die gefiltert werden können.
Die Items wiederum haben einen Count auf die Kriterien. Das sind die Häkchen, die du klickst.
Wenn eines von beiden die Anzahl 0 aufweist, führt ein Zugriff auf ein Element zu einem Fehler.
Du kannst mit "On Error GoTo" die Fehler stumpf abfangen oder mit einem If ...Count > 0 oder If not ... Is Nothing. Auch On zeigt, ob überhaupt gefiltert wird.
Schau dir nochmal meinen Code an.
Nein ich glaube mein anliegen wurde noch nicht ganz verstanden. Bitte geben Sie mir den Code, der eine Funktion ausführt sobald ein Filter angewendet wurde. D.h. wenn ich auf einen Filter gehe und die Filterung ändere, dann soll eine Funktion ausgeführt werden.
? Das habe ich versucht, mit meinem Code umzusetzen. Wenn ich den Filter ändere, erscheint die Meldung "Der Filter wurde geändert".
Falls das bei dir nicht klappt, bitte ich um eine genauere Fehlerbeschreibung oder mehr Code.
Ich habe deinen Code copy&paste'ed, füge noch 3 Creteria's mehr hinzu, aber welche Tabelle muss ich wählen statt Table2?
Bei mir ist Tabelle2 das Tabellenblatt, auf dem in einem Bereich gefiltert wird. Das kann bei dir Tabelle1 oder ein ganz anderes sein. Das müsstest du dann anpassen.
Bevor du ganz viele Kriterien einbaust, würde ich sicherstellen, dass es mit nur einem Kriterium funktioniert.
Fang einfach an und bau das Programm dann aus.
Die Zeile mit dem SUBTOTAL benötigst du, um das Calculate auszulösen. Eventuell müsstest du das in eine andere Zelle schreiben, wenn M1 für etwas anderes bereits belegt ist.
Ich habe alle Tabellen, die ich per intelli-sense sehen, ausprobliert, es geht nirgends ...
Welche Fehlermeldung bekommst du? Wo hält der Debugger an?
Ich habe Tabelle10 eingestellt, weil das die höchst-nummerrierte ist und das Blatt auch das letzt-erstellte ist.
Tabelle10.AutoFilter.Filters.Count -> gibt mir 0 aus (komisch)
Tabelle10.AutoFilter.Filters.Item(1).On -> bleibt hängen, Error: Objektvariable oder Width-Block Variable nicht festgelegt.
Hast du denn auf Tabelle10 einen AutoFilter deklariert?
Wenn es keinen gibt, schlagen folgende Befehle fehl. Das heißt, du musst ...Filters.Count > 0 mit If abfragen.
Ich habe jetzt Feierabend, ich schreibe morgen weiter um daws Problem zu lösen.
Ich habe autofilter, ich hatte die falsche Tabelle deklariert ^^. aber trotzdm geht es net
Tabelle3.AutoFilter.Filters.Item(1).On das ist nie Wahr
Das Filtern des Autofilters ist zunächst mal kein Ereignis. Jedoch kannst du das Calculate-Ereignis hernehmen um deinen Code auszulösen.
Wie nennt sich dieses Ereignis? Ist es möglich einen kleinen VB-Codeausschnitt darzustellen?
PS: Du kannst dir auch die Filterkriterien merken (ist sicherer):