Makro: farbige Zellen zählen aktualisieren

Frage_Ausrufezeichen · 24. September 2012 um 14:39

Hallo Hilfsbereite,

zum Sachverhalt: ich soll für meine Arbeit ein Blanko-Kalender erstellen indem die Urlaubs- und Krankheitstage der einzelnen Kollegen aufgeführt werden. Die Krankheitstage werden durch eine Blau-Färbung der Zelle dargestellt.
Mithilfe eines Makros habe ich die blauen Zellen einer Zeile ( somit eines Mitarbeiters) gezählt. Werden nun weitere Bau-Färbungen getätigt zählt sie das Makro erst wenn ich ihm den Befehl gebe. Da es sich um ein Blanko handelt und der Nutzer sich null mit Excel auskennt sollte es sich automatisch aktualisieren.
Ich habe bereits in Foren gestöbert und „timer“ oder „autoexec“ ausprobiert - leider erfolglos.
Ebenfalls eine automatische Aktualisierung bräucht ich bei einer erweiteren Filterung.
(Es reicht wenn es sich immer nach dem Speichern oder dem neu Öffnen aktualisiert).

Wäre nett wenn mir da jemand helfen könnte.
Vielen Dank schonmal.

Thomas_Ramel · 24. September 2012 um 14:54

Grüezi Frage-Ausrufezeichen

zum Sachverhalt: ich soll für meine Arbeit ein Blanko-Kalender
erstellen indem die Urlaubs- und Krankheitstage der einzelnen
Kollegen aufgeführt werden. Die Krankheitstage werden durch
eine Blau-Färbung der Zelle dargestellt.

Das ist nicht gut durchdacht und sollte anders gelöst werden - für Berechnungen aufgrund von Formatierungen ist Excel nicht ausgelegt.

Füge für jede Möglichkeit ein Kürzeln in die Zellen ein und lass die Zellen dann über die Bedingte Formatierung entsprechend einfärben.
Das sieht optisch dann genauso aus…

Mithilfe eines Makros habe ich die blauen Zellen einer Zeile (
somit eines Mitarbeiters) gezählt. Werden nun weitere
Bau-Färbungen getätigt zählt sie das Makro erst wenn ich ihm
den Befehl gebe. Da es sich um ein Blanko handelt und der
Nutzer sich null mit Excel auskennt sollte es sich automatisch
aktualisieren.

…aber Du kannst ganz einfach die Kürzel mit ZÄHLENWENN() zählen und brauchst keine Krücken für die Aktualisierung.

Mit freundlichen Grüssen

Thomas Ramel

MVP für MS-Excel -

Frage_Ausrufezeichen · 24. September 2012 um 20:24

Vielen Dank Thomas Ramel für deine Antwort und den Vorschlag mit dem Kürzel. Das ist eine sehr gute Idee!
Die Formel „Zählenwenn“ ist mir auch bekannt.
Allerdings habe ich schon im vorhinein meinen „Augabenstellern“ klar gemacht, dass es auf dem Weg mit Kürzeln leichter wäre. Ich habe leider kein Gehör gefunden, da teilweise Bemerkungen in die Zellen geschrieben werden soll ( - aber keine standardisierten Texte - ).

Der Zählen der Einfärbung klappt ja auch. Aber leider nur durch Befehl. Was ausgerechnet ich irgendwie automatisieren soll =)

Aber vll will man sich das Leben mit dem Automatisieren nur unnötig leicht machen ^^

Würde mich trotzdem freuen wenn ich dank Hilfe den Anforderungen meines Chefs gerecht werden würde.

Markus_K_ln · 24. September 2012 um 23:38

Hi,

was sagt denn Chef zu Thomas Ramels Antwort? Schließlich ist Thomas MVP für Excel (Chef vermutlich nicht, oder?). Ich will ja nicht unhöflich sein, aber mir fällt zu solchen Vorgaben nur ein:
es ist, als würde ein Disponent seinem Lkw-Fahrer nicht nur vorschreiben, wie und wo er zu fahren hätte (was ich für vollkommen normal halte), sondern auch, wie er den Fahrersitz einzustellen hat.

Damit würde er dem Lkw-Fahrer-Facharbeiter vorschreiben, wie er sein Arbeitszeug benutzen soll, obwohl er selbst kein Lkw-Fahrer-Facharbeiter ist. (Disponenten sind das seltenst, sie sind Facharbeiter anderer Art). Meistens tun Disponenten das aber gar nicht einmal in böser oder herrschsüchtiger Absicht, sondern einfach, weil sie im Laufe ihres Arbeitslebens so viele Lkw-Fahrer disponiert und deren Rückmeldung gehört haben, daß sie meinen, auch vom Lkw-Fahren etwas zu verstehen (in einigen Teilbereichen stimmt das sogar).

So hoffe ich, daß dieser Post Dir ermöglicht, Dein Arbeitszeug Excel fachgerecht in der hier vorgeschlagenen Weise zu benutzen. Dazu könntest Du Chef von diesem Post in Kenntnis zu setzen. Schließlich zeigt dieser Post auch Initiative, nämlich daß Du Dich bemühst, die Vorgaben von Chef zu erfüllen.

HTH.

Markus

Thomas_Ramel · 25. September 2012 um 06:58

Grüezi Frage-Ausrufezeichen…

Allerdings habe ich schon im vorhinein meinen
„Augabenstellern“ klar gemacht, dass es auf dem Weg mit
Kürzeln leichter wäre. Ich habe leider kein Gehör gefunden, da
teilweise Bemerkungen in die Zellen geschrieben werden soll (

aber keine standardisierten Texte - ).

…dafür wäre dann wohl der Kommentar einer Zelle besser geeignet, denke ich…

Der Zählen der Einfärbung klappt ja auch. Aber leider nur
durch Befehl. Was ausgerechnet ich irgendwie automatisieren
soll =)

Wirklich Automatisieren lässt sich das nicht - Du kannst zwar aus deinem Code (wie sieht der denn eigentlich aus?) eine Funktion machen, doch auch diese wird sich nicht automatisch aktualisieren wenn sie sich auf die Formatiereung einer Zelle bezieht.
Denn die Veränderung einer Formatierung löst in Excel nunmal kein Ereignis aus, das als ‚Trigger‘ für eine Neuberechnung verwendet werden könnte und so kannst Du nie sicher sein, dass das angezeigte Ergebnis auch wirklich stimmt.

Du kannst nun zwar hingehen und z.B. bei jeder Verschiebung der Zellenmarkierung eine Neuberechnung anstossen (per VBA), doch das erzeugt einen Overhead, der sich meiner Ansicht nach nicht rechtfertigt und der gerade bei umfangreichen Berechnungen in der Mappe auch stark an die Zeit geht.

Eine Funktion die Zellen aufgrund der Hintergrundfarbe zählt habe ich hier mal entwickelt:

Function ZählenWennFarbe(Bereich As Range, \_
 SuchFarbe As Variant, \_
 Optional bolFont As Boolean = False) As Double

'Idee von Melanie Breden, © [email protected] / 13.10.2004 / 11.12.2004
'Funktion zur Anwendung von ZÄHLENWENN mit Hintergrundfarbe
'oder Schriftfarbe als Kriterium
'
'Die Parametereingabe erfolgt in derselben Reihenfolge wie in der Funktion
'ZÄHLENWENN():
' - Der erste Parameter erwartet den Suchbereich
' - Der zweite Parameter erwartet einen Zellbezug
' (Hintergrund/Schriftfarbe) oder Farbindex (Zahl)
' Farbindex '0' zählt Zellen ohne farbigen
' Hintergrund/Standard-Schriftfarbe
' - Der dritte Parameter erwartet Wahr/Falsch für die Festlegung
' ob nach Hintergrund- oder Schriftfarbe gezählt werden soll
'
' Bsp =ZählenWennFarbe(A1:A10;A1;0) =ZählenWennFarbe(A1;A1:A10;1)
' =ZählenWennFarbe(A1:A10;3;0) =ZählenWennFarbe(3;A1:A10;1)

'Zur automatischen Aktualisierung im Tabellenblatt den folgenden Term
'anhängen: +(0\*JETZT()) und F9 drücken
'Also z.B. wie folgt: =ZählenWennFarbe(A1:A10;A1)+(0\*JETZT())

Dim intColor As Integer
Dim rngCell As Range

 If bolFont Then
 If IsObject(SuchFarbe) Then
 intColor = SuchFarbe(1).Font.ColorIndex
 Else
 intColor = SuchFarbe
 End If

 For Each rngCell In Bereich
 If rngCell.Font.ColorIndex = intColor Then
 ZählenWennFarbe = ZählenWennFarbe + 1
 End If
 Next

 Else
 If IsObject(SuchFarbe) Then
 intColor = SuchFarbe(1).Interior.ColorIndex
 Else
 intColor = SuchFarbe
 End If

 For Each rngCell In Bereich
 If rngCell.Interior.ColorIndex = intColor Then
 ZählenWennFarbe = ZählenWennFarbe + 1
 End If
 Next

 End If
End Function

Mit freundlichen Grüssen

Thomas Ramel

MVP für MS-Excel -

falken · 25. September 2012 um 12:41

Hallo ?!;
wie werden denn die Zellen gefärbt? Etwa von Hand? Das könnte man doch auch per Makro machen, welches man z. B. per Makro aufruft.
In diesem Makro ließen sich dann doch ein paar weitere Zeilen unterbringen, die ein unsichtbares Blatt mit den bereits angesprochenen Kodierungen versehen ( gleiche Zelle, aber eben auf anderem Blatt ). Die Kodierungen könnte man per Excel-Funktion wiederum im HauptBlatt abfragen…
Wäre das ein Weg?
Freundliche Grüße
Thomas

Reinhard_fb8cb7 · 25. September 2012 um 16:18

Hallo ?!,

Ich habe bereits in Foren gestöbert und „timer“ oder
„autoexec“ ausprobiert - leider erfolglos.

autoexec kenne ich aus alten Dos-Zeiten, die autoexec.bat.
Im Zusammenhang mit Excel/Vba sagt mir das nix.
timer schon eher. Sicher, dir wurd ja schon gesagt daß
Vba leider nicht automatisch auf Zellfarbenänderung reagieren kann.
Aber man kann ein Timermakro basteln daß alle x Sekunden
die Farben neu zählt.
Interesse?

Gruß
Reinhard

Frage_Ausrufezeichen · 25. September 2012 um 18:04

Hallo,

hätte ehrlich nicht gedacht, dass man hier so schnell und viele Hilfestellungen erhält. Vielen Dank!!

@ Falken: JA. Die Färbung wird manuell vorgenommen. Immer wenn ein Mitarbeiter anruft und sich krank meldet… Meine Excel-Kenntnisse reichen leider nicht aus, um deinen Lösungsansatz ganz nachvollziehen zu können =(

@ Thomas Ramel auf die Frage wie mein Code aussieht, hier ein Ausschnitt:

Sub blau_zaehlen()
anzahl = 0
For Each Zelle In Range(„A3:AN3“)
If Zelle.Interior.ColorIndex = 5 Then
anzahl = anzahl + 1
End If
Next Zelle
Cells(3, 42).Value = anzahl

anzahl = 0
For Each Zelle In Range(„A4:AN4“)
If Zelle.Interior.ColorIndex = 5 Then
anzahl = anzahl + 1
End If
Next Zelle
Cells(4, 42).Value = anzahl

anzahl = 0
For Each Zelle In Range(„A5:AN5“)
If Zelle.Interior.ColorIndex = 5 Then
anzahl = anzahl + 1
End If
Next Zelle

… bis zur Zeile A95:AN95 hab ich das Spielchen gemacht -.- …

Sub End

Damit hat das Zählen ganz gut geklappt.
Ich werde trotzdem deine Alternative Morgen ausprobieren! Ist wahrscheinlich nicht so aufwendig - besonders wenn man mal über 95 Zeilen hat. Ich muss dazu sagen, dass ich dieses makro, code oder was es ist mir auch nur aus einem Forum rauskopiert und entsprechend angepasst habe.

@ Reinhard: Mit timer (alle 10 Sekunden) habe ich s versucht und zunächst hatte es auch geklappt. Habe leider danach noch an anderer Stelle rumprobiert und plötzlich hat der timer nicht mehr funktioniert. Auch beim neuen „einsetzten“ nicht…

Habe zwischenzeitig weiter alle Leute in meiner Umgebung mit meinem Problem genervt bis mir das präsentiert wurde:

Sub workbook_open()
blau_zaehlen
End Sub

(blau_zaehlen heißt mein anderes „makro“)

und ES FUNKTIONIERT!!
Nach dem Eintragen von neuen Einfärbungen, dem speichern, schließen und wieder öffnen sind die aktuellen Blauen Zellen nach einigen Sekunden (mit)gezählt. Normal wäre ich mit der Lösung nicht zufrieden. Da jedoch der, der die Zelle einfärbt und der der die Auswertung braucht (die ich auch noch als Diagramm darstellen darf) zwei verschiedene Personen sind und somit immer ein Speicher-Schließ-Öffen-Vorgang dazwischen ist, bin ich (mal sehen was der Chef sagt)erstmal zufrieden.
Ich werde natürlich wie gesagt EURE TIPPS auch ausprobieren. Sind vll Anwendungsleichter, ohne Notwendigkeit des Schließen und Öffnens und somit einfach BESSER!

Ob man eine Filterung… „aktualisieren“ kann, weiß ich leider noch nicht…

Bisher vielen Dank!
?!

Reinhard_fb8cb7 · 26. September 2012 um 15:48

Hallo ?!,

@ Thomas Ramel auf die Frage wie mein Code aussieht, hier ein
Ausschnitt:

probiere mal diesen Code:

Option Explicit

Sub blau\_zaehlen()
Dim anzahl As Integer, Zelle As Range, N As Long
For N = 3 To 95
 anzahl = 0
 For Each Zelle In Range("A" & N & ":AN" & N)
 If Zelle.Interior.ColorIndex = 5 Then
 anzahl = anzahl + 1
 End If
 Next Zelle
 Cells(N, 42).Value = anzahl
Next N
End Sub

@ Reinhard: Mit timer (alle 10 Sekunden) habe ich s versucht
und zunächst hatte es auch geklappt. Habe leider danach noch
an anderer Stelle rumprobiert und plötzlich hat der timer
nicht mehr funktioniert. Auch beim neuen „einsetzten“ nicht…

Kann ich nix zu sagen, kenne deinen Code nicht und weiß nicht was
du da wo rumprobiert hast.

Nach dem Eintragen von neuen Einfärbungen, dem speichern,
schließen und wieder öffnen sind die aktuellen Blauen Zellen
nach einigen Sekunden (mit)gezählt.

Nach eingigen Sekunden? *hmmh*

Ob man eine Filterung… „aktualisieren“ kann, weiß ich leider
noch nicht…

Da ist mir bislang noch unklar wie das da warum ablaufen soll.

Gruß
Reinhard