Microsoft Excel

Herbers Excel/VBA-Archiv

Makro per Worksheet_Calculate() starten

Betrifft: Makro per Worksheet_Calculate() starten von: Andreas
Geschrieben am: 14.03.2013 14:49:28

Hallo Excelprofis!

Habe mein Problem gestern schon mal geschildert. Bin auch Dank der Hilfe von Daniel schon etwas weiter.

Ich habe eine Mappe, bei der mittels Verknüpfungen 4 andere Mappe zusammengeführt werden.

Mit Hilfe eines Autofilter Makros werden alle Zeilen im Bereich A7-A109 die leer sind ausgeblendet.

Mein Problem ist jetzt noch, dass dieses Makro jedesmal ausgeführt werden muss, wenn sich im Bereich A7-A109 der Zellwert ändert, also wenn in den Ursprungsmappen ein Wert geändert wird.

Ich habe von VBA gar keine Ahnung, habe mir aber folgendes zusammengestückelt.

Private Sub Worksheet_Calculate()
Dim zelle As Range
Dim bereich As Range

Set bereich = ActiveSheet.Range("A7:A109")

For Each zelle In bereich 
If Not zelle.Value = "" Then
Call Autofilter
End Sub


Wenn jetzt bei den Ursprungsmappen etwas geändert wird, zeigt es mir in meiner Mappe folgenden Fehler an.

Call Autofilter - Unzulässige Verwendung einer Eigenschaft.

Kann mir Jemand helfen.

Danke schon mal.

mfg, Andreas

  

Betrifft: AW: Makro per Worksheet_Calculate() starten von: Klaus M.vdT.
Geschrieben am: 14.03.2013 14:59:18

  • Call Autofilter

  • Hi,

    du hast ein Makro, dass Autofilter heisst?
    Versuch mal, das Makro umzubennennen ... in irgendwas triviales, zB "HalloIchBinDasFilterMakro".
    Generell solltest du beim benennen von Makros, Variablen usw vermeiden, Worte zu benutzen die Excel bereits belegt.

    Grüße,
    Klaus M.vdT.


      

    Betrifft: AW: Makro per Worksheet_Calculate() starten von: Andreas
    Geschrieben am: 14.03.2013 15:08:21

    Hallo!

    Habe es geändert. Sieht jetzt so aus. Bringt jetzt Fehlermeldung "For ohne Next"

    Private Sub Worksheet_Calculate()
    Dim zelle As Range
    Dim bereich As Range
    
    Set bereich = ActiveSheet.Range("A7:A109")
    
    For Each zelle In bereich
    If zelle <> 0 Then
    Call Renate
    End If
    End Sub
    mfg, Andreas


      

    Betrifft: AW: Makro per Worksheet_Calculate() starten von: Klaus M.vdT.
    Geschrieben am: 14.03.2013 15:15:41

    Hallo Andreas,

    dir fehlt ein next, dafür hast du ein End If zuviel.

    For Each zelle In bereich
    If zelle <> 0 Then
    Call Renate
    End If
    ich tippe jetzt mal, tausche End IF gegen next und dann läufts. Aber die Datei zu sehen währ schon gut.
    Du hast keine Ahnung von dem was du da machst, oder (nicht bös gemeint!)?

    Grüße,
    Klaus M.vdT.


      

    Betrifft: AW: Makro per Worksheet_Calculate() starten von: Andreas
    Geschrieben am: 14.03.2013 15:31:17

    Hallo Klaus M.vdT.

    Vielen Dank für die Hilfe und nein ich habe keine Ahnung.

    Ich habe mal eine Musterdatei hochgeladen:

    https://www.herber.de/bbs/user/84364.xlsm

    mfg, Andreas


      

    Betrifft: AW: Makro per Worksheet_Calculate() starten von: Klaus M.vdT.
    Geschrieben am: 14.03.2013 15:53:16

    Hi Andreas,

    in der Musterdatei hast du den Fehler ja schon korrigiert. Und was läuft jetzt nicht?

    Grüße,
    Klaus M.vdT.


      

    Betrifft: sorry, gesehen von: Klaus M.vdT.
    Geschrieben am: 14.03.2013 15:55:14

    Hallo Andreas,
    ich ziehe mein letzten Einwand zurück.
    In deiner Musterdatei geht gar nichts!
    Bitte erkläre mal in Prosa, was du machen möchtest.
    Grüße,
    Klaus M.vdT.


      

    Betrifft: AW: sorry, gesehen von: Andreas
    Geschrieben am: 14.03.2013 16:09:12

    Hallo Klaus M.vdT.

    Danke für die Hilfe!

    Diese Datei wird über Verknüpfungen zu anderen Dateien mit Daten gefüttert. Dadurch können aber Leerzeilen entstehen die ausgeblendet werden müssen. Mit dem Makro "FilternBereich" wird das erreicht. (das Makro funktioniert auch, wenn man im Blatt FAKS den Code löscht - dieser ist ja falsch).

    Wird jetzt in den Ursprungsmappen ein Wert geändert, dazugeschrieben oder gelöscht, muss in dieser Mappe das Makro ausgeführt werden. Dabei kann es passieren, daß diese Mappe gerade geöffnet ist, oder das Makro muss beim Öffnen starten. Sind beide Mappen offen, muss der Fokus aber auf der Ursprungsmappe bleiben.

    Ich hoffe es ist verständlich!

    mfg, Andreas


      

    Betrifft: AW: sorry, gesehen von: Klaus M.vdT.
    Geschrieben am: 15.03.2013 08:51:27

    Hi Andreas,

    dein Filtern-Makro habe ich komplett ignoriert, stattdessen blende ich die Zellen innerhalb deines Makros direkt aus.
    Ganz wichtig bei sowas: Enableevents abschalten (noch wichtiger: wieder anschalten!)
    Grund: ein "calculate" ruft dein Ausblenden-Makro auf. Das Makro verursacht ein Calculate. Das ruf das Ausblenden-Makro auf. Das Makro verursacht ein Calculate. Das ruf das Ausblenden-Makro auf. Das Makro verursacht ein Calculate. Das ruf das Ausblenden-Makro auf. [...] Der Computer stützt ab, weil er 1.000.000.000 Makros gleichzeitig zu verarbeiten versucht.

    Längere Makros gehören schon aus Prinzip nicht direkt ins Calculate-Ereigniss. Ich habe das ausblenden-Makro umbenannt in "EinAusblendenLangsam", dieses Makro wird im Calculate-Ereigniss aufgerufen.
    Nebenher habe ich dir auch ein Makro "EinAusblendenSchnell" geschrieben, dass wesentlich performanter ist als die 'Krücke' mit dem Zellenweise prüfen + ausblenden .. leider ist es weniger intuitiv verständlich.

    Private Sub Worksheet_Calculate()
    EinAusblendenSchnell
    End Sub
    
    Sub EinAusblendenLangsam()
    Dim zelle As Range
    Dim bereich As Range
    Set bereich = ActiveSheet.Range("A7:A109")
    
    'Bildschirmflackern ausschalten, Trigger ausschalten
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    
    'alle Zeilen einblenden
    bereich.EntireRow.Hidden = True
    
    'jede Zeile prüfen
    For Each zelle In bereich
        'wenn Zelleninhalt null, dann diese Zeile ausblenden
        If zelle <> 0 Then
            'Call FilternBereich
            zelle.EntireRow.Hidden = False
        End If
    Next
    
    Application.ScreenUpdating = True
    Application.EnableEvents = True
    End Sub
    
    Sub EinAusblendenSchnell()
    Dim bereich As Range
    Set bereich = ActiveSheet.Range("A7:A109")
    
    'Trigger auscchalten
    Application.EnableEvents = False
    
    'alles einblenden
    bereich.EntireRow.Hidden = False
    
    'alle Zellen mit Inhalt = 0 ausblenden
    bereich.SpecialCells(xlCellTypeFormulas, 1).EntireRow.Hidden = True
    
    Application.EnableEvents = True
    End Sub
    
    Grüße,
    Klaus M.vdT.


      

    Betrifft: AW: sorry, gesehen von: Andreas
    Geschrieben am: 15.03.2013 12:01:49

    Hallo Klaus M.vdT.

    Vielen Dank für die Hilfe und Mühe!


    Habe beide Varianten getestet. Funktionieren beide Super, aber nur wenn ich die Ursprungsmappe nach einem neuen Eintrag schließe. Sind beide Dateien geöffnet, werden bei der Variante - Sub EinAusblendenLangsam() - die Zeilen der Ursprungstabelle ausgeblendet und in der Variante - Sub EinAusblendenSchnell() - kommt eine Fehlermeldung.
    Kann man statt "ActiveSheet.Range" auch einen eindeutigen Blattnamen angeben, oder gibt es eine andere Lösung.

    mfg, Andreas





      

    Betrifft: AW: sorry, gesehen von: Klaus M.vdT.
    Geschrieben am: 18.03.2013 08:28:37

    Hi Andreas,


    Kann man statt "ActiveSheet.Range" auch einen eindeutigen Blattnamen angeben
    ja. Statt dem aktiven Blatt

    Dim bereich As Range
    Set bereich = ActiveSheet.Range("A7:A109")

    eindeutig so:
    Dim bereich As Range
    Set bereich = sheets("DerNameDerTabelleInAnfuehrungsstichen").Range("A7:A109")



    Sub EinAusblendenLangsam() - die Zeilen der Ursprungstabelle ausgeblendet
    das kann nicht sein - es werden Zeilen der AKTIVEN Tabelle ausgeblendet (siehe oben). Wenn die Ursprungstabelle AKTIV ist, dann werden natürlich in ihr die Daten ausgeblendet. Abhilfe dafür hast du ja jetzt.

    in der Variante - Sub EinAusblendenSchnell() - kommt eine Fehlermeldung.
    Wenn Fehlermeldungen kommen, ist es hilfreich diese ins Forum zu posten, und dazu die Zeile in der er Auftritt. Wenn der Code bei mir nicht funktioniert hätte, hätte ich ihn ja nicht gepostet. Jetzt muss ich deinen Fehler mittels Glaskugel erraten. Mal sehen .... ist es: Runtime Error 1004: No cells where found. in der Zeile bereich.SpecialCells(xlCellTypeFormulas, 1).EntireRow.Hidden = True ?
    In deiner Musterdatei gab es Zellen, die per Formelergebniss den Inhalt 0 haben. SpecialCells(xlCellTypeFormulas, 1) sucht nach eben diesen, um sie auszublenden. Sind keine Zellen mit Formelergebniss 0 vorhanden (wenn eine echte 0 in den Zellen steht, hilft das auch nicht!) dann gibts o.g. Fehlercode. Eine richtige Fehlerbehandlung dazu werde ich jetzt nicht basteln, das überfordert dein VBA-Level. Schreib einfach on error resume next als erste Zeile in das Makro, dann werden alle Fehler ignoriert. Noch besser: Sorge dafür, dass - analog zu deiner Mustertabelle - dein Master genauso aufgebaut ist (also mit 0en als Formelergebniss). Denn dann funktioniert das Makro sogar!

    Grüße,
    Klaus M.vdT.


      

    Betrifft: AW: Vielen Dank für die Hilfe und Mühe! von: Andreas
    Geschrieben am: 18.03.2013 12:09:09

    Hallo Klaus M.vdT.

    Sorry, hätte natürlich die Fehlermeldung präzisieren müssen. Dank deiner Hilfe funktionierts jetzt aber.

    Eine schöne Woche noch.

    mfg, Andreas


      

    Betrifft: Danke für die Rückmeldung! owT. von: Klaus M.vdT.
    Geschrieben am: 18.03.2013 12:25:56

    .


     

    Beiträge aus den Excel-Beispielen zum Thema "Makro per Worksheet_Calculate() starten"