Inleiding

Dit is een nieuwe blog. In deze blog worden met enige regelmaat berichten gepost met ervaringen en wetenswaardigheden op het gebied van vooral Excel, Access en VBA. De aanleiding voor deze blog is dat ik tegen onverwachte problemen aanliep bij het instellen van voorwaardelijke opmaak met VBA. Voorwaardelijke opmaak in Excel is een erg handig hulpmiddel. Als echter in een sheet met voorwaardelijke opmaak veel cellen gekopieerd worden, dan wordt de voorwaardelijke opmaak van deze cellen ook meegekopieerd. Op zich is dat natuurlijk goed, maar Excel gaat hier niet altijd goed mee om. Bereiken voor voorwaardelijke opmaak kunnen door Excel ten onrechte worden opgesplitst, waardoor er heel veel extra regels voor voorwaardelijke opmaak in het bestand terecht kunnen komen. Hierdoor neemt de bestandsgrootte toe en neemt de snelheid af. Het kan zelfs leiden tot het crashen van Excel. Ik was met een applicatie bezig met daarin een sheet met voorwaardelijke opmaak waarbij veel gekopieerd, verplaatst en geknipt en geplakt gaat worden. Ik besloot daarom de voorwaardelijke opmaak in te stellen met VBA. Bij het activeren van de betreffende sheet wordt daarbij eerst de voorwaardelijke opmaak verwijderd en daarna opnieuw ingesteld (N.B.: bij het opstarten van dit werkboek wordt altijd eerst een andere sheet getoond. Om de sheet met voorwaardelijke opmaak te zien moet deze eerst aangeklikt worden, zodat het activate-event altijd wordt getriggerd. In andere gevallen kan de code beter in een module gezet worden en kan deze bijvoorbeeld ook bij het Workbook_Open-event aangeroepen worden).

 

Voorwaardelijke opmaak met VBA

Het instellen van voorwaardelijke opmaak met VBA is op zich niet zo moeilijk dus ik dacht: ‘dat is zo gepiept’. Maar dat viel in de praktijk tegen. Ik liep namelijk tegen twee voor mij onbekende problemen aan die mij in eerste instantie wat hoofdbrekens kosten. Zoals gezegd wilde ik eerst de voorwaardelijke opmaak verwijderen en daarna opnieuw instellen. Dit wilde ik doen met het Sheet_Activate-event. Ik had daar in beginsel de volgende code voor gemaakt:

Private Sub Worksheet_Activate()
    With ActiveWorkbook.Sheets("Betterwin").Range("B3:C100")
        .FormatConditions.Delete
        .FormatConditions.Add Type:=xlExpression, Formula1:="=AND(OR($B3<>"""",$C3<>""""),ISODD(ROW()))"
        .FormatConditions(1).Interior.Color = vbMagenta
        .FormatConditions.Add Type:=xlExpression, Formula1:="=AND(OR($B3<>"""",$C3<>""""),ISEVEN(ROW()))"
        .FormatConditions(2).Borders(xlBottom).LineStyle = xlContinuous
        .FormatConditions(2).Borders(xlBottom).Color = vbMagenta
    End With
End Sub

Op zich niks bijzonders en redelijk rechttoe-rechtaan: eerst de opmaak van het bereik verwijderen en daarna twee nieuwe opmaakregels op basis van formules toevoegen. Het bereik krijgt hiermee een soort tabel-achtige look.

 

Hoezo UserInterFaceOnly=True?

Maar bij het testen van deze code kreeg ik een foutmelding op de regel met .FormatConditions.Delete  (fout 1004: ‘door toepassing of object gedefineerde fout’). Ik begreep in eerste instantie niet hoe dat kon. Maar na enige tijd zoeken en testen kwam ik er achter dat dit te maken had met de werkbladbeveiling. Het werkblad was beveiligd met een wachtwoord, maar ik had ingesteld UserInterFaceOnly=True. Dus dan kunnen met macros’s/VBA wél wijzigingen aan het werkblad worden doorgevoerd, zonder eerst met Unprotect de beveiliging van het werkblad te hoeven verwijderen. Normaal gesproken werkt dat prima, dus ik had daar de oorzaak niet gezocht. Maar tot mijn verbazing geldt de instelling UserInterFaceOnly blijkbaar niet voor het met VBA instellen van voorwaardelijke opmaak. Dus ik heb uiteindelijk mijn code als volgt aangepast:

Private Sub Worksheet_Activate()
    ActiveWorkbook.Sheets("Betterwin").Unprotect Password:="1234"
    With ActiveWorkbook.Sheets("Betterwin").Range("B3:C100")
        .FormatConditions.Delete
        .FormatConditions.Add Type:=xlExpression, Formula1:="=AND(OR($B3<>"""",$C3<>""""),ISODD(ROW()))"
        .FormatConditions(1).Interior.Color = vbMagenta
        .FormatConditions.Add Type:=xlExpression, Formula1:="=AND(OR($B3<>"""",$C3<>""""),ISEVEN(ROW()))"
        .FormatConditions(2).Borders(xlBottom).LineStyle = xlContinuous
        .FormatConditions(2).Borders(xlBottom).Color = vbMagenta
    End With
    ActiveWorkbook.Sheets("Betterwin").Protect Password:="1234", UserInterFaceOnly:=True
End Sub

 

Formules als FormulaLocal

Maar dit gaf nog steeds een foutmelding (fout 5:’ongeldige procedure aanroep of ongeldig argument’), maar nu op de volgende regel. Ook hier begreep ik in eerste instantie niets van. Zou er wat mis zijn met de syntax van de formules? Ik heb diverse zaken geprobeerd, maar niets hielp. Ook op de Developer Reference-site van Microsoft kwam ik niet verder. Uiteindelijk kwam ik er achter dat de formules als lokale formule moeten worden ingeven… Alle formules in VBA moeten ingegeven worden als Engelstalige formule (tenzij bewust gekozen wordt voor FormulaLocal), maar de formules bij voorwaardelijk opmaak als lokale formule. In mijn geval dus als Nederlandstalige formule. Ik vind dat erg vreemd en inconsequent. Dus de VBA-code heb ik daarna als volgt aangepast:

Private Sub Worksheet_Activate()
    ActiveWorkbook.Sheets("Betterwin").Unprotect Password:="1234"
    With ActiveWorkbook.Sheets("Betterwin").Range("B3:C100")
        .FormatConditions.Delete
        .FormatConditions.Add Type:=xlExpression, Formula1:="=EN(OF($B3<>"""";$C3<>"""");IS.ONEVEN(RIJ()))"
        .FormatConditions(1).Interior.Color = vbMagenta
        .FormatConditions.Add Type:=xlExpression, Formula1:="=EN(OF($B3<>"""";$C3<>"""");IS.EVEN(RIJ()))"
        .FormatConditions(2).Borders(xlBottom).LineStyle = xlContinuous
        .FormatConditions(2).Borders(xlBottom).Color = vbMagenta
    End With
    ActiveWorkbook.Sheets("Betterwin").Protect Password:="1234", UserInterFaceOnly:=True
End Sub

 

Workaround voor alle taal-versies

Dit werkte eindelijk zonder foutmeldingen. Maar omdat mijn applicatie ook door mensen gebruikt gaat worden die geen Nederlandstalige Excel-versie hebben, gaat dat problemen geven, want bovenstaande code gaat in een anderstalige Excel-versie niet werken. Daarom heb ik daarvoor een workaround gemaakt. Ik sla eerst de formule als Engelstalige formule op in een lege cel van het werkblad. Vervolgens lees ik deze formule uit als formule in de lokale taal en ik koppel deze formule aan de voorwaardelijke opmaak. Tenslotte verwijder ik de tijdelijk opgeslagen formule uit de cel. Op deze manier werkt deze oplossing in alle taal-versies. De uiteindelijke code is geworden:

Private Sub Worksheet_Activate()
    Dim sLocalFormula1 As String, sLocalFormula2 As String
    Application.ScreenUpdating = False
    With ActiveWorkbook.Sheets("Betterwin")
        .Unprotect Password:="1234"
        Application.EnableEvents = False
        .Range("A2").Formula = "=AND(OR($B3<>"""",$C3<>""""),ISODD(ROW()))"
        sLocalFormula1 = .Range("A2").FormulaLocal
        .Range("A2").Formula = "=AND(OR($B3<>"""",$C3<>""""),ISEVEN(ROW()))"
        sLocalFormula2 = .Range("A2").FormulaLocal
        .Range("A2").ClearContents
        .Range("B3").Select 'bleek alleen nodig in Excel 2007, omdat anders de formules niet correct werden ingesteld
        Application.EnableEvents = True
    End With
    With ActiveWorkbook.Sheets("Betterwin").Range("B3:C100")
        .FormatConditions.Delete
        .FormatConditions.Add Type:=xlExpression, Formula1:=sLocalFormula1
        .FormatConditions(1).Interior.Color = vbMagenta
        .FormatConditions.Add Type:=xlExpression, Formula1:=sLocalFormula2
        .FormatConditions(2).Borders(xlBottom).LineStyle = xlContinuous
        .FormatConditions(2).Borders(xlBottom).Color = vbMagenta
    End With
    ActiveWorkbook.Sheets("Betterwin").Protect Password:="1234", UserInterFaceOnly:=True
    Application.ScreenUpdating = True
End Sub

Al met al had voor mij het instellen voor van voorwaardelijke opmaak met VBA veel meer voeten in aarde dan ik in eerste instantie dacht.

4 reacties op Voorwaardelijke opmaak met VBA: enkele hindernissen

  • Om te voorkomen dat iedere keer als het werkblad aktief wordt de hele code opnieuw wordt uitgevoerd; en ter voorkoming van internationale problemen:

    Private Sub Worksheet_Snb_Activate()
        With Sheet1.Range(&quot;B3:C100&quot;).FormatConditions
            If .Count = 0 Then
              .Add(2, , &quot;=(($B3&quot;&quot;&quot;&quot;)+($C3&quot;&quot;&quot;&quot;))*ISODD(ROW())&quot;).Interior.Color = vbMagenta
               With .Add(2, , &quot;=(($B3&quot;&quot;&quot;&quot;)+($C3&quot;&quot;&quot;&quot;))*ISEVEN(ROW())&quot;).Borders(-4107)
                 .LineStyle = 1
                 .Color = vbMagenta
               End With
            End If
        End With
    End Sub
    • Hallo snb,

      Bedankt voor je reactie. Ik neem aan dat in de formule-regels -tekens moeten worden toegevoegd. Deze regels worden dan:

      .Add(2, , &quot;=(($B3&lt;&gt;&quot;&quot;&quot;&quot;)+($C3&lt;&gt;&quot;&quot;&quot;&quot;))*ISODD(ROW())&quot;).Interior.Color = vbMagenta
      With .Add(2, , &quot;=(($B3&lt;&gt;&quot;&quot;&quot;&quot;)+($C3&lt;&gt;&quot;&quot;&quot;&quot;))*ISEVEN(ROW())&quot;).Borders(-4107)

      Ook met deze verbetering werken de formules niet in mijn Nederlandstalige versie. Ik krijg het alleen maar werkend door ISODD, ISEVEN en ROW te vervangen door de Nederlandstalige variant. Dus is ontkom er volgens mij nog steeds niet aan om voor een internationale toepassing de formules eerst in een lege cel op te slaan en dan uit te lezen als lokale formule.

      Het advies om eerst met .Count het aantal voorwaarden te tellen is prima. Bedankt daarvoor. In de praktijk ga ik dat zodanig veranderen dat de formatconditions alleen worden verwijderd en opnieuw weer ingesteld, als het aantal meer dan 2 is.

  • Blogsoftware heeft vaak moeite met speciale tekens.

    Ik zou niet weten waarom je bestaande voorwaardelijke opmaakregels telkens zou verwijderen en steeds weer dezelfde regels zou toevoegen.
    Mijn voorkeur zou de workbook_open gebeurtenis zijn: eenmalig lijkt me voldoende.

    • Dat kan ook. Dit had ik ook aangegeven de tekst (zie laatste regel van de inleiding). De betreffende applicatie wordt vrijwel dagelijks gebruikt, maar het werkblad met VO hoeft maar af en toe gebruikt te worden (ongeveer 1x per maand). Vandaar dat ik in dit geval gekozen heb voor de Worksheet_Activate-gebeurtenis.

Geef een reactie

Het e-mailadres wordt niet gepubliceerd.