Office: (Office 2010) nicht gesperrte Zellen färben / diese Zellenfarbe nicht ausdrucken

Helfe beim Thema nicht gesperrte Zellen färben / diese Zellenfarbe nicht ausdrucken in Microsoft Excel Hilfe um das Problem gemeinsam zu lösen; Hallo zusammen, Ich habe ein größeres Excel-Formular mit vielen Blättern und viel VBA-Code. Alle Eingabefelder (=nicht gesperrte Zellen) sind gelb,... Dieses Thema im Forum "Microsoft Excel Hilfe" wurde erstellt von Abramovici, 11. Juli 2015.

  1. nicht gesperrte Zellen färben / diese Zellenfarbe nicht ausdrucken


    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.

    :)
     
    Abramovici, 11. Juli 2015
    #1
  2. Hallo Nicolas,
    Schau Dir mal die Lösung von Karin unter "Beispiele mit VBA / Drucken ohne Farbe" an:

    excel-inn

    Gruß
    Aloys
     
    aloys78, 12. Juli 2015
    #2
  3. Hallo Aloys,

    Vielen Dank für die sehr schnelle Antwort !

    Ich habe die Datei "drucken_ohne_farbe.xlsm" auf Beverly's Excel-Inn 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.
     
    Abramovici, 12. Juli 2015
    #3
  4. nicht gesperrte Zellen färben / diese Zellenfarbe nicht ausdrucken

    Hallo Nicolas,
    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
     
    aloys78, 12. Juli 2015
    #4
  5. 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. Juli 2015
    #5
  6. 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. Juli 2015
    #6
  7. 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
     
    Abramovici, 13. Juli 2015
    #7
  8. nicht gesperrte Zellen färben / diese Zellenfarbe nicht ausdrucken

    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
     
    aloys78, 13. Juli 2015
    #8
  9. 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
     
    Abramovici, 13. Juli 2015
    #9
Thema:

nicht gesperrte Zellen färben / diese Zellenfarbe nicht ausdrucken

Die Seite wird geladen...
  1. nicht gesperrte Zellen färben / diese Zellenfarbe nicht ausdrucken - Similar Threads - gesperrte Zellen färben

  2. Sortieren bei gesperrten Zellen nicht möglich

    in Microsoft Excel Hilfe
    Sortieren bei gesperrten Zellen nicht möglich: Hallo zusammen, ich versuche eine Tabelle zu sortieren, bei der ich einige Zellen gesperrt habe. Beim Sperren habe ich die Option "Sortieren" extra ausgewählt. Trotzdem bekomme ich die Meldung...
  3. Als Inhalte einfügen, nur Werte erlauben, in nicht gesperrte Zellen

    in Microsoft Excel Hilfe
    Als Inhalte einfügen, nur Werte erlauben, in nicht gesperrte Zellen: Hallo, ich möchte in einer Excel-Datei (ca. 100 Tabellenregister) beim Kopieren nur Werte einfügen, möglich zu machen. Ausschneiden+Drag&Drop habe ich bereits per Makro ausgeschaltet. Ich habe...
  4. Nur Inhalte einfügen erlauben, in nicht gesperrte Zellen

    in Microsoft Excel Hilfe
    Nur Inhalte einfügen erlauben, in nicht gesperrte Zellen: Hallo, ich habe eine Excel-Datei (100 Tabellenregister) für die ich eine Formel gesucht habe, die es erlaubt, beim Kopieren nur Werte einfügen, möglich zu machen. Ausschneiden+Drag&Drop habe ich...
  5. DoubleClick verhindern in gesperrten Zellen

    in Microsoft Excel Hilfe
    DoubleClick verhindern in gesperrten Zellen: Hallo, Ihr Excel Spezialisten Ich habe hier eine Mitarbeiter-Liste (als Tabelle 'tblAktiv' formatiert) , die nicht 'per Hand' bearbeitet werden soll. Ich habe bis auf die Spalte 'Match'...
  6. zelle I6="text" dann zellen J6&K6 eingabe sperren und umgekehrt soll I6 gesperrt werd

    in Microsoft Excel Hilfe
    zelle I6="text" dann zellen J6&K6 eingabe sperren und umgekehrt soll I6 gesperrt werd: Hallo ich möchte wie im Anhang Zellen gegeneinander sperren. Wenn ich ich in Zelle I6 (per Dropdown) einen Text auswählen, sollen die Zellen J6 & K6 gesperrt werden und wenn ich in der...
  7. Bug: Zellen immer wieder temporär gesperrt

    in Microsoft Excel Hilfe
    Bug: Zellen immer wieder temporär gesperrt: Hallo zusammen, vielleicht kann mir jemand helfen, da mich dieser Fehler langsam wahnsinnig macht. Egal welches Dokument ich nehme, es kommt immer wieder sporadisch vor (meist nach Zelle oder...
  8. Gesperrte Zellen, Formatieren ja, aber Texteingabe sperren

    in Microsoft Excel Hilfe
    Gesperrte Zellen, Formatieren ja, aber Texteingabe sperren: Hallo Leute, ist es möglich, Zellen in Excel so zu sperren, das man zwar die Zellen formatieren kann (Farblich verändern z.B.), aber das man keinen Text eingeben kann?
  1. Diese Seite verwendet Cookies, um Inhalte zu personalisieren, diese deiner Erfahrung anzupassen und dich nach der Registrierung angemeldet zu halten.
    Auf dieser Website werden Cookies für die Zugriffsanalyse und Anzeigenmessung verwendet.
    Wenn du dich weiterhin auf dieser Seite aufhältst, akzeptierst du unseren Einsatz von Cookies.
    Information ausblenden