Office: Änderung Schaltflächenfarbe beim Anklicken

Hallo zusammen.

Ich habe folgende Frage:

Auf meinem Arbeitsblatt werden Zellen durch Anklicken von Schaltflächen, auf denen Makros hinterlegt sind, mit Nachnamen befüllt.
Die Schaltflächen sind in der Grundfarbe grau, ändern sich durch einmaliges Anklicken auf grün, durch nochmaliges Anklicken auf rot, um optisch darzustellen, dass der Nachname entweder schon 1x (grün) oder mehrmals (rot) auf dem Arbeitsblatt vorhanden ist.

Dies erreiche ich mit folgendem Befehl:

Sub Nachname()
'
' Name Makro
ActiveCell.FormulaR1C1 = "Nachname"

' Button Farbe ändern ??
Dim Farbe(0 To 2)

Farbe(0) = RGB(222, 222, 222)
Farbe(1) = RGB(0, 204, 0)
Farbe(2) = RGB(255, 0, 0)

With ActiveSheet.Shapes(Application.Caller).Fill.ForeColor
Select Case .RGB
Case Farbe(0): .RGB = Farbe(1)
Case Farbe(1): .RGB = Farbe(2)
Case Farbe(2): .RGB = Farbe(2)
Case Else: .RGB = Farbe(0)
End Select
End With

End Sub

Mein Problem:
Die angeklickten Schaltflächen bleiben auch in der Farbe grün/rot, wenn ich die Nachnamen vom Arbeitsblatt wieder lösche.

Um die Schaltflächenfarbe nach Löschung der Nachnamen manuell zu "neutralisieren" (wieder auf grau zu setzen), habe ich zusätzlich einen CommandButton mit folgendem Befehl eingesetzt:

Private Sub CommandButton1_Click()

Dim intI As Integer

For intI = 4 To 134
ActiveSheet.Shapes(intI).Fill.ForeColor.RGB = RGB(222, 222, 222)
Next intI

End Sub

Damit "neutralisiere" ich natürlich alle Schaltflächen auf einmal, unabhängig davon, ob einzelne Nachnamen noch auf dem Arbeitsblatt vorhanden sind.

Meine Frage:
Ist es möglich, dass sich die Schaltflächenfarbe automatisch von rot auf grün auf grau ändert, angepasst daran, ob und wie oft der entsprechende Nachname auf dem Arbeitsblatt vorhanden ist?

Ich bedanke mich schon mal im Voraus für eure kreative Ideen.

Grüße

 

Hi,

lade bitte eine Beispielmappe hoch.

---

 

Anbei die Beispielmappe.

- das Arbeitsblatt ist geschützt, nur die Zellen A2 - A16 sind zum Befüllen ungeschützt
- die Nachnamen aus Spalte D sollen einzeln frei wählbar per Klick in Zellen A2 - A16 eingefügt werden (Reihenfolge beliebig).
- durch das Anklicken färbt sich die entsprechende Schaltfläche in der Spalte D grün.
- wird die gleiche Schaltfläche mehrfach angeklickt, färbt sich die entsprechende Schaltfläche rot, so dass man sehen kann, dass der Nachname mehrfach in der Spalte A eingefügt wurde (optische Kontrollfunktion).
- entfernen der Nachnamen funktioniert entweder einzeln durch direktes Löschen des betreffenden Nachnamens aus der jeweiligen Zelle in Spalte A oder durch Klick auf die Schaltfläche "Alles leeren", dann werden alle Nachnamen aus der Spalte A gelöscht. In beiden Fällen bleiben die Schaltflächen der Nachnamen in Spalte D jedoch in den zuletzt angezeigten Farben.
- durch den CommandButton "Neutralisieren", werden alle Schaltflächen in Spalte D wieder grau, auch von den Nachnamen, die sich noch in Spalte A befinden

Meine Frage ist, ob man es hinkriegt, dass sich die Farben der Schaltflächen in Spalte D automatisch angepasst an die Einträge in Spalte A ändern (grau, wenn der betreffende Nachname nicht in Spalte A eingefügt ist, grün wenn er 1x vorhanden ist, rot, wenn er mehrfach vorhanden ist).

 

Code:

Sub Zuruecksetzen()
    Dim shaShape As Shape
    Dim intAnzahl As Integer
    For Each shaShape In ActiveSheet.Shapes
        If shaShape.Type = msoAutoShape Then
            If shaShape.DrawingObject.Caption <> "Alles Leeren" Then
                intAnzahl = Application.CountIf(Range("Aufgaben"), shaShape.DrawingObject.Caption)
                If intAnzahl = 0 Then
                    shaShape.Fill.ForeColor.RGB = 14606046
                ElseIf intAnzahl = 1 Then
                    shaShape.Fill.ForeColor.RGB = 52224
                Else
                    shaShape.Fill.ForeColor.RGB = 255
                End If
            End If
        End If
    Next shaShape
End Sub

---

 

Hallo Beverly.

Vielen Dank für deinen Code. Sieht ja echt wild aus.
Könntest du meine Beispielmappe mit deinem ergänzten Code hier einstellen? Dann könnte ich sehen, wo und wie du den genau platziert hast. Bei mir will es nicht so ganz klappen.

 

Hi,

im Anhang die Mappe.

Übrigens kannst du in deinem Code noch eine ganze Menge vereinfachen/verbessern.

---

 

Perfekt, vielen Dank. Hätte ich niemals hinbekommen.

Wie du an meinem Code vermutlich siehst, bin ich blutjunger Anfänger und von daher für Tipps und Tricks immer offen.

 

Hi,

man muss nicht den kompletten Code zum Eintragen des Namens und Setzen der Farbe für jede Schaltfläche schreiben. Sich mehrfach wiederholende Operationen kann man in Unter-Prozeduren auslagern, indem man an diese die notwendigen Informationen übergibt. Jede Schaltfläche ist jetzt mit dem Makro "NameEintragen" verknüpft und ruft die Unterprozedur

Code:

Sub FarbeSetzen(strName As String)

auf, wobei die Aufschrift der Schaltfläche - also der Name - übergeben wird. In der Unterprozedur wird der übergebene Name gezählt und anhand der Anzahl die entsprechende Farbnummer an die Variable lngFarbe übergeben, mit der dann in der Haupt-Prozedur die Schaltfläche gefärbt wird. Die Variable lngFarbe ist außerhalb aller Prozeduren deklariert - das ist erforderlich, damit sie in mehreren Prozeduren verwendet werden kann.

Setze einfach mal einen Haltepunkt auf die Prozedur Sub EintragName(), drücke eine Schaltfläche und verfolge dann im Einzelschrittmodus (mit F8), was der Code macht - dann wird sicher vieles leichter nachvollziehbar.

Der Code sollte übrigens auch funktionieren wenn kein Blattschutz gesetzt ist, denn es wird (sicherheitshalber) geprüft, ob sich die aktive Zelle im Bereich "Aufgaben" befindet.

---

 

Hallo Beverly.
Vielen Dank für deine Mühen, das hilft mir tatsächlich weiter, allerdings steige ich so richtig immer noch nicht durch.
Die von mir eingestellte Beispielmappe sollte zur Verdeutlichung meiner grundsätzlichen Thematik dienen, war vom Aufbau daher eher schlicht gehalten. Meine tatsächliche Arbeitsmappe ist komplexer, da ich vom Grundsatz dachte, dass wenn die Codes einmal passen, sie "universal" einsetzbar sind, unabhängig vom konkreten Aufbau und Layout der Mappe. Da hab ich wohl tatsächlich zu einfach gedacht.

Wenn ich deine erstellten Codes aus der Beispielmappe nehme (in der sie wunderbar funktionieren) und sie in die tatsächliche Arbeitsmappe übertrage, dann funktionieren sie leider nicht mehr bzw. werden Fehlermeldungen angezeigt. Könnte es daran liegt, weil die zu befüllende Zellen nicht direkt untereinander angeordnet sind? Zumindest springt der Cursor nach Befüllen immer nur eine Zelle tiefer, die allerdings dann geschützt ist, statt zur nächsten ungeschützten Zelle.

Anbei füge ich mal die Arbeitsmappe an, so wie sie tatsächlich aussieht, wobei das nur eine von mehreren Versionen ist, was den Aufbau/ das Layout angeht. In den anderen Versionen sind die geschützten/ungeschützten Zellen teilweise anders angeordnet, wodurch sich dann auch der Bereich "Aufgaben" ändert. Im Optimalfall sollten die Codes auf alle Versionen anwendbar sein.
In der angehängten Mappe habe deine Codes übernommen, den "Neutralisieren-Button" entfernt und die Schaltflächen mit dem richtigen Makro versehen.

Vielleicht kannst du dir mal anschauen, wo die Fehler liegen und es mir erklären?!

(Zur Verdeutlichung: Die grauen und roten Zellen sind ungeschützt, diese werden mit dem Button "Alles leeren" geleert. In die grauen Zellen kommen individuelle Eintragungen, in die roten Zellen die Eintragungen mittels der Schaltflächen. Weiterhin bestehen in geschützten Zellen Verknüpfungen zu einem weiteren Arbeitsblatt, damit entsprechende Eintragungen durch Betätigen der Schaltflächen erfolgen ( die Schaltflächen sind grundsätzlich Namen, die Verknüpfungen dazu passende Telefonnummern).

Ich bedanke mich schon mal im Voraus und entschuldige mich für die Umstände.

 

Hi,

das Problem ist nicht nur, dass dein Bereich diskontinuierlich ist, sondern auch, dass du verbundene Zellen verwendest - diese müssen anders behandelt werden als nicht verbundene.
Schau mal, ob jetzt alles so funktioniert wie gewünscht.

Und noch ein Tipp für die Zukunft: beim Hochladen einer Beispielmappe immer darauf achten, dass die Bedingungen denen in der Originalmappe 1:1 entsprechen, dann kann man sich besser darauf einstellen

---

 

Das mit dem zukünftigen Hochladen werde ich definitiv beherzigen. Sorry für die Mehrarbeit.

Ich habe deine Mappe mal durchgetestet, da sind mir tatsächlich paar Sachen aufgefallen.
Bevor du dich da aber nochmal dranmachst, noch mal mein Hinweis, dass das nur Sinn macht, wenn die Codes dann universal sind. Nicht, dass du da zu viel Zeit und Energie reinbringst und mir jede Arbeitsmappe aufs neue individuell gestalten musst, sobald ich da wieder eine kleine Änderung habe.

Falls dein Ehrgeiz allerdings geweckt sein sollt, hier die Sachen, die mir aufgefallen sind:

- Zelle M3: Cursor springt nicht weiter, entsprechende Schaltfläche färbt sich direkt rot
- Zellen P20-P22. Fehlermeldung: Typen unverträglich
- Zellen F28, I28, L 28: Cursor springt nach links statt nach unten
- Zelle C31: Fehlermeldung: Index außerhalb des gültigen Bereichs, entsprechende Schaltfläche bleibt grau, Cursor springt nicht weiter
- Zellen F31, I31, L31: Fehlermeldung: Typen unverträglich, entsprechende Schaltfläche bleibt grau, Cursor springt nicht weiter
- Zellen in den Zeilen 33 – 48: Fehlermeldung: Typen unverträglich, entsprechende Schaltfläche bleibt grau, Cursor springt nicht weiter, bei Betätigen "Alles leeren" Fehlermeldung: Typen unverträglich
- Zellen in den Zeilen 49-54: Fehlermeldung: Typen unverträglich, entsprechende Schaltfläche bleibt grau
- Allgemein: wenn man Eintragungen einzeln löscht bleibt die entsprechende Schaltflächen in der zuletzt angezeigten Farbe

Da soll mal einer schlau draus werden.

 

1. Zelle M3 steht in deiner Namensdefinition doppelt drin, deshalb kann der Code die nächste Adresse nicht finden

2. du hast für einige Zellen einen Zellbereich angegeben (z.B. $P$20:$Q$22), deshalb kann die betreffende Adresse nicht gefunden werden, weil Split die Zellbereiche nicht in Einzelzellen zerlegen kann - folglich Laufteitfehler "Typen unverträglich"

3. Zelle C31: die Split Funktion hat nur eine begrenzte Anzahl an Feldern zur Verfügung (43) - der Rest wird abgeschnitten, folglich können die betreffenden Zellen nicht gefunden werden und der Code gibt deshalb den Index-Fehler aus.

Um 2. und 3. zu umgehen kann man die Split-Funktion nicht verwenden - man muss also eine Schleife über alle Zellen des Bereichs machen und sie in ein Array einlesen, dann können auch zusammhängende Zellbereiche angegeben werden

4. Zellen F28, I28, L 28 - Cursor springt deshalb nach links, weil deine Reihenfolge in der Bereichsdefinition so vorgegeben ist. Die Auflistung der Zelladressen muss bei der Namensgefinition also in derselben Reihenfolge erfolgen wie deine gewünschte Reihenfolge ist.

5. um einzelne Eintragungen zu löschen und die Farben anzupassen ist im Worksheet_Change-Ereignis vor der letzten End If-Zeile diese Zeile zu ergänzen: If Target.Cells(1) = "" Then Zuruecksetzen

Punkte 2, 3 und 5 habe ich realisiert - Punkte 1 und 4 musst du selbst umsetzen.

---

 

Punkte 1 und 4 habe ich umgesetzt, eine Neusortierung der Bereichsdefinition hat beide Probleme gelöst und es funktioniert alles ohne Probleme.
Auch gut, dass man zusammenhängende Zellbereiche angeben kann, mehr als 123 Einzelzellen konnte ich in der Bereichsdefinition nicht eingeben, was definitiv zu wenig gewesen wären. Anscheinend ist es auf diese Anzahl begrenzt. Aber mit den zusammengefügten Zellbereichen kein Problem mehr.

Eine Sache ist mir noch aufgefallen:
Die Änderung der Schaltflächenfarbe ist ja von Löschvorgängen abhängig, entweder per Button "Alles löschen" oder per Einzellöschung. Bei direkter Überschreibung von Einträgen, egal ob händisch oder per Schaltfläche, ändert sich die Schaltflächenfarbe des überschriebenen Eintrages jedoch nicht. Erst, wenn man irgendwo auf dem Arbeitsblatt wieder etwas löscht.
Ist dieses Problem auch noch lösbar?!
Falls nicht direkt, würde ich ansonsten den Button aus deiner vorherigen Beispielmappe (RE_schaltflaechen_faerben_in_abheangigkeit.xlsm) einfügen, der hat ja die Schaltflächenfarbe beim Betätigen aktualisiert, was ganz wunderbar geklappt hat.

Und wofür ist der Code

Sub aaaa()
MsgBox Selection.Address
End Sub

?
Alle anderen Codes funktionieren anscheinend auch ohne diesen.

 

Ändere die Codezeile

Code:

If Target.Cells(1) = "" Then Zuruecksetzen

in

Code:

Zuruecksetzen

dann wird jegliche Änderung im Bereich berücksichtigt - gleichgültig ob ein Eintrag gelöscht oder von Hand geändert wird.

Die Sub aaaa() kannst du löschen - ich habe damit nur getestet welche Zelladresse bei verbundenen Zellen ausgegeben wird.
Für die Zukunft gebe ich dir allerdings den guten Rat: vermeide UNBEDINGT verbundene Zellen, denn sie verkomplizieren unnötig 1. eine VBA-Lösung und 2. die Handhabung des Tabellenblattes, falls der Tabellenaufbau (aus was für Gründen auch immer) einmal geändert werden muss.

---

 

Problem ist gelöst, alles funktioniert wie gewünscht.
Vielen Dank an alle, die an dem Projekt mitgearbeitet haben.