Helpmij.nl
Helpmij.nl
Helpmij.nl
Steun Helpmij.nl! Klik hier     Computerprobleem? Klik hier!

Quote

Weergeven resultaten 1 tot 16 van 16

Onderwerp: Alleen niet-geblokkeerde cellen andere kleur geven

  1. #1
    Vraag is opgelost

    Alleen niet-geblokkeerde cellen andere kleur geven

    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
    Bijgevoegde bestanden Bijgevoegde bestanden

  2. #2
    zo?
    Bijgevoegde bestanden Bijgevoegde bestanden

  3. #3
    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

  4. #4
    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

    Code:
    Sub CelOntkleuren()
    
    For Each cl In ActiveSheet.UsedRange
        If cl.Locked = False Then cl.Interior.Color = xlNone
    Next
        
    End Sub
    Code:
    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...
    Laatst aangepast door roeljongman : 11 februari 2019 om 14:26

  5. #5
    Mega Senior E v R's avatar
    Geregistreerd
    22 oktober 2008
    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:

    Code:
    Worksheets("Blad1").PageSetup.BlackAndWhite = True
    Gr. Eric

  6. #6
    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;
    Code:
    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;
    Code:
    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

  7. #7
    Hoi,

    Nou, ik ben weer een stapje verder.
    Code:
    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?

  8. #8
    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

    Code:
    =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
    Code:
    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.
    Bijgevoegde bestanden Bijgevoegde bestanden

  9. #9
    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;
    Code:
    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....

  10. #10
    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
    Code:
    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

  11. #11
    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:
    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
    Groetjes,

    Jan Karel Pieterse
    Excel MVP jkp-ads.com

  12. #12
    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..

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

    Jan Karel Pieterse
    Excel MVP jkp-ads.com

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


    Code:
    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
    Laatst aangepast door roeljongman : 26 maart 2019 om 10:30

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

  16. #16
    Werkt als een tierelier, bedankt!

  17. Dit topic is automatisch gesloten omdat er sinds vier maanden niet meer op gereageerd is.

    Indien gewenst kan de topicstarter een verzoek tot heropening indienen.

Berichtenregels

  • U mag geen nieuwe vragen starten.
  • U mag niet reageren op berichten.
  • U mag geen bijlagen versturen.
  • U mag uw berichten niet bewerken.
  •  
Helpmij.nl
Helpmij.nl

Helpmij.nl en business

Partners
Sponsoren