Alleen niet-geblokkeerde cellen andere kleur geven

[Marco9999]

Goedemorgen,

Ik heb een werkblad gemaakt welke door collega's ingevuld moet gaan worden.
De cellen waar niets ingevuld hoeft te worden zijn geblokkeerd, het werkblad is beveiligd.

De cellen die wel ingevuld moeten worden heb ik een kleurtje gegeven en zijn uiteraard niet geblokkeerd.

Als ik het werkblad afdruk wil ik graag dat de gekleurde invulvelden wit gemaakt worden, dat staat wat netter op de print.
Na afdrukken mogen de cellen wit blijven.

Nu zoek ik een VBA-script om alleen de niet-geblokkeerde cellen op het werkblad wit te maken bij afdrukken.
Heb het al eerder geprobeerd met voorwaardelijke opmaak maar dat is geen suc6.

Iemand een iedeetje?

Groet,
Marco

[emields]

zo?

[Marco9999]

Hoi,

Nog niet helemaal. Ik heb in de werkmap zo'n 20 bladen zitten met erg veel cellen op verschillende plaatsen.
Ik zoek eigenlijk een formule die zegt:

Alle niet geblokkeerde cellen = wit

[roeljongman]

Ik kon zo snel niet de juiste events vinden om het in of uit te schakelen voor en na printen.
maar deze macro's in een Module1 werken, je kunt ze dan naar wens aanroepen in de events

Sub CelOntkleuren()

For Each cl In ActiveSheet.UsedRange
    If cl.Locked = False Then cl.Interior.Color = xlNone
Next
    
End Sub

Sub CelKleuren()

For Each cl In ActiveSheet.UsedRange
    If cl.Locked = False Then cl.Interior.Color = 10079487
Next
    
End Sub

PS: jou voorbeeld bestand had alleen locked cells dus daar zal je eerst een paar cellen op unlocked moeten zetten om het te testen...

[E v R]

Je kunt, tenminste als zwart-wit print voldoende is en er geen andere kleuren zijn, de page-setup op zwart-wit zetten.
Dan is 1 eenmalige handeling voldoende per sheet:

Worksheets("Blad1").PageSetup.BlackAndWhite = True

[Marco9999]

Nou, het werkt toch nog niet helemaal lekker.
Ik doe het nu anders; d.m.v. van een script haal ik eerst de beveiliging van alle bladen af;

For Each ws In ActiveWorkbook.Sheets
ws.Unprotect
Next ws

Nu wil ik alle bladen selecteren en alle cellen wit maken, hier kom ik nog niet helemaal uit, ik heb bovenstaand script uitgebreid;

Sub Geelnaarwit()

For Each ws In ActiveWorkbook.Sheets
ws.Unprotect
Next ws
    
For Each cl In ActiveWorkbook
cl.Interior.Color = 10079487
Next
    
End Sub

Hier loopt Excel op vast, iemand enig idee?

Alvast bedankt,
Marco

[Marco9999]

Hoi,

Nou, ik ben weer een stapje verder.

Sub Geelnaarwit()

Application.ScreenUpdating = False

For Each ws In ActiveWorkbook.Sheets
ws.Unprotect
    
For Each cl In ActiveSheet.UsedRange
cl.Interior.Color = xlNone
Next cl
    
Next ws
    
Application.ScreenUpdating = True
    
End Sub

Dit werkt op zich goed, het duurt alleen crisislang voordat alle cellen nagelopen zijn.
Daarom wil ik nu dus alleen de cellen die kleur 10079487 hebben veranderen naar kleur xlNone.

Iemand een ideetje?

[roeljongman]

Tsja dat is een belangrijk nadeel van for each loops op grotere bereiken.
Ik ken geen snelle manier om alle cellen met een specifieke kleur te selecteren, het moet voor zover ik weet dus cel voor cel.
jou extra voorwaarde gaat daarbij niet heel erg helpen omdat nog steeds elke cel beoordeeld moet worden op kleur. het bereik wordt dus niet kleiner.

Bij mijn eerste reactie had ik geen inzicht in de omvang van de sheet. Dus leek het een werkbare oplossing, blijkt van niet.. dus dan "terug naar de tekentafel"..

Ik zou de aanpak veranderen en de kleuren uit alle invoercellen verwijderen. en daarna een voorwaardelijke opmaak (VO) instellen die de invoercellen kleurt op basis van het feit dat ze niet beveiligd zijn.
dat kan middels de formule CEL("bescherming";A1)=0

Het enige wat je nu nog nodig hebt is een cel die als "schakelaar" fungeert, ik heb gekozen in dit voorbeeld voor cel F2 in deze cel zet je de waarde WAAR of ONWAAR, Deze cel F2 moet je ook kunnen wijzigen!
in de VO maak je nu een combinatie van 2 testen

=EN(CEL("bescherming";A1)=0;$F$2)

vervolgens stel je een bereik in waardoor alle invoercellen hier invallen je mag gewoon een groot bereik opgeven dat zal de performance niet beinvloeden.

als laatste heb je een hele kleine macro nodig

Sub ToonVerbergInvoercellen()

Range("F2") = Not Range("F2")
End Sub

Deze doet niks meer en niks minder dan WAAR in ONWAAR veranderen en weer terug

In het voorbeeld heb ik de macro aan een knop gehangen elke keer als je die indrukt zullen de kleuren verdwijnen over verschijnen dankzij de ingestelde VO

Ik heb in het voorbeeld op blad1 de cellen nu zichtbaar gehouden en de beveiliging uit staan.
maar in de 2e tab Blad1 Beveiligd heb ik een 1 op 1 kopie van blad 1 maar dan kolom E en F verborgen en de sheetbeveiliging aangezet.
Je kunt dan nog steeds middels de knop de kleuren aan en uit zetten ZONDER de beveiliging te hoeven op te heffen. (zolang F2 maar niet beveiligd staat)

De knop is ook optioneel, die hoef je niet te gebruiken Je kunt in een andere macro ook deze macro aanroepen of als je voor een printmacro hebt kun je ook in die macro een regel opnemenWorksheets("jousheetnaam").Range("F2")= False om zeker te zijn dat de kleuren voor printen worden uitgeschakeld.

zo lang verhaal voor relatief simpele oplossing.

[Marco9999]

Ja, het wordt een beetje omslachtig allemaal.
Ik heb het eerder geprobeerd met de voorwaardelijke opmaak maar daar loop ik weer tegen andere problemen aan. Voorwaardelijke opmaak gebruik ik dan ook liever niet.

Laten we het eens het over een andere boeg gooien.

Ik haal van alle werkbladen de beveiliging af en alle cellen moeten wit worden.
Ik heb het met dit script geprobeerd maar dat werkt niet helemaal;

Sub Geelnaarwit()

    Application.ScreenUpdating = False

    For Each ws In ActiveWorkbook.Sheets
    ws.Unprotect
    
    Cells.Select
    With Selection.Interior
    .Pattern = xlNone
    .TintAndShade = 0
    .PatternTintAndShade = 0
    End With
    
    Next ws

    Application.ScreenUpdating = True
    
End Sub

Zit vast nog een ***lig foutje in....

[roeljongman]

Het is maar wat je ***lig wilt noemen..
je probeert 340+ miljard cellen te muteren dus ja kan zijn dat het wat traag is of zelfs dat excel ermee ophoudt...

middels Cells.Select selecteer je het hele werkblad, dat bestaat uit 1.048.576 rijen en 16.384 kolommen te samen 17,179 miljard cellen op 1 werkblad. Volgens een eerder bericht wil je 20 werkbladen op deze manier bewerken dus ja.. best een beetje veel al je bedenkt dat een gemiddeld A4 tje 350 cellen bevat.

Probeer het eens met .usedrange dat zal alvast een stuk schelen

Sub Geelnaarwit()

Application.ScreenUpdating = False

For Each ws In ActiveWorkbook.Sheets
    ws.Unprotect
    With ws.UsedRange.Interior
        .Pattern = xlNone
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
Next ws

Application.ScreenUpdating = True
    
End Sub

[jkpieterse]

Het kan heeeeeeeeel veel sneller. Gebruik een celstijl voor de invoercellen, zie https://jkp-ads.com/Articles/styles00nl.asp voor meer info. Wat je vervolgens nodig hebt is hele snelle en simpele code:

Sub ToggleStyleColors(sStyleName As String, lOriginalColor As Long)
    Dim oST As Style
    Dim oSh As Worksheet
    For Each oSh In Worksheets
        oSh.Unprotect
    Next
    For Each oST In ActiveWorkbook.Styles
        If oST.Name Like sStyleName Then
            If oST.IncludePatterns Then
                If oST.Interior.Color = lOriginalColor Then
                    oST.Interior.Color = vbWhite
                Else
                    oST.Interior.Color = lOriginalColor
                End If
            End If
        End If
    Next
    For Each oSh In Worksheets
        oSh.Protect
    Next
End Sub

Sub Demo()
    ToggleStyleColors "Input*", 10079487
End Sub

[Marco9999]

Nou, ik heb 'm aan de praat met de oplossing van roeljongman, hartstikke bedankt!!
De oplossing van Jan Karel zal ongetwijfeld ook prima werken maar daar ga ik me later eens in verdiepen.

Nog 1 probleempje; kan ik ook 1 blad uitsluiten? Ik heb een blad genaamd "Voorblad" waar ik de kleurtjes wél op wil laten staan.
Ik heb al geprobeerd om het voorblad te verbergen en na het witten weer zichtbaar te maken maar dan kleurt ie het voorblad ook wit..

[jkpieterse]

Ik zou sowieso eens goed kijken naar de celstijlen, heel nuttig voor ieder Excel bestand.

[roeljongman]

dat kun je ondervangen met een if test
even ongetest. letop de Not voor de test ws.name anders doet ie juist alleen voorblad..

Sub Geelnaarwit()

Application.ScreenUpdating = False

For Each ws In ActiveWorkbook.Sheets
if Not ws.name = "Voorblad" then
    ws.Unprotect
    With ws.UsedRange.Interior
        .Pattern = xlNone
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
end if
Next ws
Application.ScreenUpdating = True
    
End Sub

[emields]

Zijn het in alle bladen, behalve voorblad, dezelfde cellen die ontgrendeld zijn?

[Marco9999]

Werkt als een tierelier, bedankt!