PDA

Vollständige Version anzeigen : nicht gesperrte Zellen färben / diese Zellenfarbe nicht ausdrucken


Abramovici
13.07.2015, 09:13
Hallo zusammen,

Ich habe ein größeres Excel-Formular mit vielen Blättern und viel VBA-Code.

Alle Eingabefelder (=nicht gesperrte Zellen) sind gelb, damit der Benutzer diese auf dem ersten Blick erkennt.

Ich möchte die gelbe Zellenfarbe nicht ausdrucken (da diese viele gelbe Tinte kostet bzw. beim Schwarz/Weiß-Ausdruck grau ist und dadurch der Ausdruck nicht mehr so gut lesbar insb. nach Kopieren/Scannen).

2 Möglichkeiten habe ich schon probiert, leider sind beide unbefriedigend:

MÖGLICHKEIT 1: Excel-Option "Schwarzweißdruck"
In den Optionen "Seiten einrichten" kann man "Schwarzweißdruck" ankreuzen. Dann wir die gelbe Zellenfarbe zwar nicht ausgedruckt, alle andere Zellen werden aber auch weiß gedruckt und die Textfarbe und Graphiken und Bilder nur noch schwarz/grau (das ist hier nicht gewünscht).

MÖGLICHKEIT 2: bedingte Formatierung + Excel-VBA
Bei jedem Blatt habe ich folgende bedingte Formatierung hinzugefügt:
"=ZELLE("Schutz";A1)=0" --> Zelle GELB

Damit werden alle nicht gesperrte Zellen gelb angezeigt.

Dazu brauche ich dann ziemlich viel VBA-Code:
- diese bedingte Formatierung wird auch bei neuen Blättern hinzugefügt
- Vor dem Ausdrucken wird bei allen Blättern diese bedingte Formatierung gesucht und "gelb" durch "keine Farbe" ersetzt.
- Nach dem Ausdrucken wird die bedingte Formatierung aller Blätter wieder auf gelb gesetzt.
- Die bedingte Formatierung muss manchmal temporär gelöscht werden (wenn ich bestimmte Zellen Rot oder Grün markieren will, um den Benutzer auf Fehler / wichtige Eingaben hinweise)

Das alles habe ich mit VBA programmiert. Es funktioniert Super.

Nur jetzt das PROBLEM:
Ab ca. 40 Blätter in der Mappe wird die Datei unstabil. Dann stürzt die Datei ab oder ich bekomme Meldungen wie "Datei kann nicht gespeichert werden".

Leider brauchen wir ab und zu bis zu 100 Blätter.

Anscheinend hat Excel damit ein Problem, wenn es zu viele bedingte Programmierungen gibt (Excel-Speichergrenze?).

LÖSUNGSIDEEN?
Hat jemand eine Idee, um das Problem zu lösen?
- Kann ich z.B. die bedingte Formatierung nur einmal für die ganze Mappe definieren (statt für jedes Blatt)? Das wäre super. Ich habe aber keinen Weg dazu gefunden.
- Gibt es andere Excel-Funktionen / VBA-Tricks?

Für eine Lösung oder auch nur Hinweise / Ideen (die ich dann gern auch selber ausprobiere), wäre ich sehr dankbar

Viele schöne Grüße
Nicolas

PS: Ich habe schon einigen Stunden in mehreren Foren und mit Google gesucht, leider bis jetzt ohne Erfolg.

aloys78
13.07.2015, 09:38
Hallo Nicolas,
Ich möchte die gelbe Zellenfarbe nicht ausdrucken
Schau Dir mal die Lösung von Karin unter "Beispiele mit VBA / Drucken ohne Farbe" an:

excel-inn (http://excel-inn.de/)

Gruß
Aloys

Abramovici
13.07.2015, 10:13
Hallo Aloys,

Vielen Dank für die sehr schnelle Antwort !

Ich habe die Datei "drucken_ohne_farbe.xlsm" auf http://excel-inn.de/ angeschaut.

So ähnlich kann ich es ja sicher auch machen, bei mir wäre es nur leicht anders:

In der Routine "Workbook_BeforePrint":
- durch alle Blätter laufen
- durch alle Zellen laufen
- wenn Zelle nicht gesperrt, dann keine Farbe
- ausdrucken
- durch alle Blätter laufen
- durch alle Zellen laufen
- wenn Zelle nicht gesperrt, dann gelb

Das wird sicher funktionieren, bei Mappen mit ca. 100 Blättern dauert das ganze doch ein bisschen --> der Benutzer muss sich gedulden.

Deswegen hatte ich die Hoffnung, dass es eine schnellere / elegantere Lösung gibt (z.B. mit bedingter Formatierung: da muss ich nicht alle einzelne Zellen aller Blätter nacheinander entfärben und wieder färben).

Wenn es mit bedingter Formatierung aber wirklich nicht geht, dann hast du Recht, werde ich wohl es so machen müssen.

aloys78
13.07.2015, 10:27
Hallo Nicolas,
Das wird sicher funktionieren, bei Mappen mit ca. 100 Blättern dauert das ganze doch ein bisschen
Das kann ich nicht nachvollziehen; ich habe Karin's Funktion in einem Add-in abgelegt und benutze sie dauernd aus den gleichen Gründen wie Du.

Wenn Du bezüglich der Performance Bedenken hast, dann realisiere doch mal testweise nur 1 Datenblatt und miss die Zeit mit/ohne VBA.

Gruß
Aloys

Abramovici
13.07.2015, 15:09
Hallo Aloys,

Jetzt bin ich mit der Anpassung meines VBA-Codes durch. Ich habe 14 Routinen anpassen müssen (wie gesagt das ist ein relativ großes Code mit ca. 80 Routinen in 20 Modulen)

Jetzt funktioniert die gelbe Markierung der nicht gesperrten Zellen mit direkter Zellenbearbeitung und nicht mehr mit bedingter Formatierung.

Wie befürchtet ist es schon deutlich langsamer geworden:

Bei einer Mappe mit 65 Blättern dauert meine Routine Farbe():
- altes Code (mit bedingter Formatierung): 2 Sek.
- neues Code (mit direkter Zellenfarbenänderung): 10 Sek.

(NB: die Routine färbt auch weitere Zellen grau oder grün mit weiteren Kriterien und zeichnet Zellenränder neu).

Das ist noch akzeptabel aber schon ein bisschen lang.

Ich denke, ich kann es aber beschleunigen:
momentan prüfe und ggf. färbe ich jede Zelle nach einander:

Für jedes Blatt:

For Each Zelle In Sheet.UsedRange
If Zelle.Locked = False Then Zelle.Interior.ColorIndex = Farbe_Eingabe
Next Zelle

Das sollte aber schneller gehen, wenn ich zuerst alle Zellen prüfen und dann alle nicht gesperrte Zellen auf einmal gelb einfärbe. Das probiere ich noch.

Abramovici
13.07.2015, 15:29
gut, es hat geklappt wie gedacht: jetzt dauert die Routine Farbe() bei der gleichen Mappe mit 65 Blättern nur noch 4 Sek.

Es ist immer noch doppelt so lang, wie mit der bedingten Formatierung. Das ist jetzt aber akzeptabel und es funktioniert (die Mappe mit 65 Blättern mit der bedingten Formatierung kann ich gar nicht speichern!).

Falls es jemanden interessiert, hier das Code:

#################################
Sub NichtGesperrteZellen_GELB(Sheet As Worksheet)

Dim Zelle As Range
Dim Range_Nicht_Gesperrt As Range 'Bereich aller nicht gesperrten Zellen

Application.ScreenUpdating = False
'Bildschirmanzeige wird ab jetzt nicht mehr bei jeder Änderung aktualisiert

Set Range_Nicht_Gesperrt = Sheet.Range("A1")
'Range_Nicht_Gesperrt wird initialisiert (beinhaltet erstmal nur Zelle A1)

For Each Zelle In Sheet.UsedRange
'Range_Nicht_Gesperrt dann mit allen nicht gesperrten Zellen erweitert

If Zelle.Locked = False Then
Set Range_Nicht_Gesperrt = Union(Range_Nicht_Gesperrt, Zelle)
End If

Next Zelle

'den ganzen Bereich der nicht gesperrten Zellen auf einmal gelb färben
Range_Nicht_Gesperrt.Interior.ColorIndex = Farbe_Eingabe

'hier keine "Application.ScreenUpdating = True" sonst wird bei Nutzung dieser Routine für die ganze Mappe
'die Anzeige nach jedem Blatt aktualisiert

End Sub

Abramovici
13.07.2015, 15:39
und dazu die Routine zur Färben aller Blätter der Mappe:

###################

Sub NichtGesperrteZellen_GELB_ganzeMappe()

Dim Blatt As Worksheet

Application.ScreenUpdating = False

For Each Blatt In ThisWorkbook.Worksheets

Call NichtGesperrteZellen_GELB(Blatt)

Next Blatt

Application.ScreenUpdating = True

End Sub

###################

ähnlich habe ich dann auch diese Routinen zum Färben in WEIß statt GELB:
- NichtGesperrteZellen_WEIß(Sheet As Worksheet)
- NichtGesperrteZellen_WEIß_ganzeMappe()
(gleiches Code wie GELB aber mit Farbe "xlNone", also eigentlich ohne Farbe)

###################
und dann das ganze vor / nach dem Drucken nutzen:

Private Sub Workbook_BeforePrint(Cancel As Boolean)

Application.ScreenUpdating = False
'Bildschirmanzeige wird ab jetzt nicht mehr bei jeder Änderung aktualisiert

Cancel = True
'sonst würde nach "End Sub" wieder ein Ausdruck starten
'(da wir hier in der Routine "Workboook_beforePrint" sind)

'alle nicht gesperrte Zellen Weiß statt Gelb färben (--> Ausdruck billiger)
Call NichtGesperrteZellen_WEIß_ganzeMappe

Application.EnableEvents = False
'damit die nächste Zeile (PrinOut)
'diese Routine (Workbook_BeforePrint) nicht neu startet,
'(sonst endlose Schleife)

ThisWorkbook.ActiveSheet.PrintOut 'ausgewähltes Blatt ausdrucken
'(Mein Code ist hier eigentlich komplizierter, für den Fall, dass man mehrere Blätter ausdruckt, hier aber gekürzt)

Application.EnableEvents = True

'alle nicht gesperrte Zellen wieder Gelb färben
Call NichtGesperrteZellen_GELB_ganzeMappe

Application.ScreenUpdating = True
'Bildschirmanzeige wird wieder aktualisiert


End Sub

aloys78
13.07.2015, 15:57
Hallo Nicolas,

mußt Du über die Suche nach nicht gesperrten Zellen gehen oder kennst Du die Eingabezellen ?
In diesem Fall könntest Du mit der Union-Anweisung alle Eingabebereiche in einer Range zusammenfassen. Und diese Range würde dann mit einer Anweisung eingefärbt bwz farblich in Grundstellung gesetzt. Das ginge natürlich wesentlich schneller.

Gruß
Aloys

Abramovici
13.07.2015, 16:32
Hallo Aloys,

Danke für den Tipp.

Den Bereich der nicht gesperrten Zelle kenne ich, da ich ihn selber festgelegt habe. Das sind aber relativ viele einzelne Zellen verteilt auf dem Blatt und bei jedem Blatt ist es anders.

Daher wäre es aufwendig es, diesen Range mit VBA zu definieren und wenn ich mein Formular ändere (z.B. neue Eingabefelder), müsste ich den Range im VBA-Code dann anpassen.

Ich habe aber das Code jetzt trotzdem eleganter gestaltet:
- statt separate Routinen (für GELB und WEIß) aber ich jetzt nur 1 Routine(und die Farbe als Variable)
- den Range von nicht gesperrten Zellen muss ich jetzt nicht mehr mit der Zelle "A1" anfangen
- der Range der nicht gesperrten Zellen wird zuerst zusammengestellt und dann auf einmal gefärbt

'########################
Sub NichtGesperrteZellen_färben(Sheet As Worksheet, Farbe As String)

Dim Zelle As Range
Dim Mind_1_Zelle_nicht_gesperrt As Boolean
Dim Range_Nicht_Gesperrt As Range 'Bereich aller nicht gesperrten Zellen

Application.ScreenUpdating = False

Mind_1_Zelle_nicht_gesperrt = False
'Variable wird mit False initialisiert
'wird später zu True geändert, wenn erste nicht gesperrte Zelle gefunden

'Für jede Zelle vom Blatt
For Each Zelle In Sheet.UsedRange

'If diese Zelle nicht gesperrt ist
If Zelle.Locked = False Then

'wenn das mind. die 2. nicht gesperrte Zelle ist, "Range_Nicht_Gesperrt" um diese Zelle erweitern
If Mind_1_Zelle_nicht_gesperrt = True Then
Set Range_Nicht_Gesperrt = Union(Range_Nicht_Gesperrt, Zelle)

'wenn das die erste nicht gesperrte Zelle ist, "Range_Nicht_Gesperrt" mit dieser Zelle initialisieren
Else
Set Range_Nicht_Gesperrt = Zelle
Mind_1_Zelle_nicht_gesperrt = True
End If

End If

Next Zelle

'Wenn mind. 1 nicht gesperrte Zelle gefunden wurde:
If Mind_1_Zelle_nicht_gesperrt = True Then

'den ganzen Bereich der nicht gesperrten Zellen auf einmal färben
Range_Nicht_Gesperrt.Interior.ColorIndex = Farbe

End If

'hier keine "Application.ScreenUpdating = True" sonst wird bei Nutzung dieser Routine für die ganze Mappe
'die Anzeige nach jedem Blatt aktualisiert

End Sub

'#########################

Sub NichtGesperrteZellen_färben_ganzeMappe(Farbe As String)

Dim Blatt As Worksheet

Application.ScreenUpdating = False

For Each Blatt In ThisWorkbook.Worksheets

Call NichtGesperrteZellen_färben(Blatt, Farbe)

Next Blatt

Application.ScreenUpdating = True 'Bildschirmanzeige wird wieder aktualisiert

End Sub