wijzig taal:

Inleiding

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.

Als in een sheet met voorwaardelijke opmaak veel gekopieerd, verplaatst en geknipt en geplakt gaat worden, dan kan het verstandig zijn om bij het activeren van de sheet eerst de voorwaardelijke opmaak te verwijderen en daarna opnieuw in te stellen. In dit artikel komt aan de orde hoe dat gedaan kan worden en dan is er met name aandacht voor de problemen die daarbij een rol kunnen spelen en wat hieraan gedaan kan worden.

De hier besproken suggesties kunnen overal toegepast worden waar problemen/foutmeldingen ontstaan bij het instellen van voorwaardelijke opmaak met VBA.

Instellen voorwaardelijke opmaak met VBA

Het instellen van de voorwaardelijke opmaak met VBA is op zich niet heel moeilijk. Een goede start is vaak om de voorwaardelijke opmaak eerst handmatig in te stellen en daarbij de macro recorder aan te zetten, zodat je kunt zien welke code daarbij gegenereerd wordt. Hierna volgt een voorbeeld van code waarbij de betreffende range een table-achtige look krijgt. Eerst wordt daarbij de huidige voorwaardelijke opmaak verwijderd, waarna deze weer opnieuw wordt ingesteld. In dit geval wordt daarbij de Worksheet_Activate gebeurtenis gebruikt. Een punt van aandacht is dat deze gebeurtenis niet getriggerd wordt bij de 1e sheet die te zien is, nadat een werkboek is geopend. Dit kan zo nodig meegenomen worden in het Workbook_Open gebeurtenis.

Private Sub Worksheet_Activate()
    With ActiveWorkbook.Sheets("WorksheetsVBA").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

Beveiligde sheets

Als de sheet beveiligd is, dan volgt er met voorgaande code een foutmelding op de regel met .FormatConditions.Delete (fout 1004: ‘door toepassing of object gedefineerde fout’). Dat is op zich logisch, maar deze foutmelding volgt ook als UserInterFaceOnly=True is ingesteld. Normaal gesproken kun je met deze instelling met VBA de sheet gewoon wijzigen zonder daarbij de beveiliging van de sheet te verwijderen. Maar in dit geval werkt dat dus blijkbaar niet. Dus op beveiligde sheets moet eerst de beveiliging worden verwijderd en daarna weer worden teruggeplaatst. De code wordt dan:

Private Sub Worksheet_Activate()
    ActiveWorkbook.Sheets("WorksheetsVBA").Unprotect Password:="1234"
    With ActiveWorkbook.Sheets("WorksheetsVBA").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("WorksheetsVBA").Protect Password:="1234", UserInterFaceOnly:=True
End Sub

Niet Engelstalige Excel versies

Maar bij niet Engelstalige Excel-versies levert bovenstaande code nog steeds een foutmelding op (fout 5:’ongeldige procedure aanroep of ongeldig argument’), maar nu op de volgende regel. De oorzaak is dat in dit geval de formules als lokale formule moeten worden ingevoerd. Dit is heel verwarrend, maar normaal gesproken moet in VBA altijd de Engelstalige formule worden gebruikt (tenzij bewust gekozen wordt voor FormulaLocal), maar hier dus niet. Voor de Nederlandstalige Excel versie moet de code dan worden:

Private Sub Worksheet_Activate()
    ActiveWorkbook.Sheets("WorksheetsVBA").Unprotect Password:="1234"
    With ActiveWorkbook.Sheets("WorksheetsVBA").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("WorksheetsVBA").Protect Password:="1234", UserInterFaceOnly:=True
End Sub

Oplossing die werkt in alle taalversies

Als een bestand in elke taalversies moet kunnen werken, dan moet de code nog verder aangepast worden. Eerst wordt dan de formule als Engelstalige formule opgeslagen in een lege cel van het werkblad. Vervolgens wordt deze formule uitgelezen in de lokale taal en die formule wordt dan gebruikt in de voorwaardelijke opmaak. Tenslotte wordt de tijdelijk opgeslagen formule weer uit de cel verwijderd. Op deze manier werkt deze oplossing in alle taal-versies. De uiteindelijke code wordt dan:

Private Sub Worksheet_Activate()
    Dim sLocalFormula1 As String, sLocalFormula2 As String
    Application.ScreenUpdating = False
    With ActiveWorkbook.Sheets("WorksheetsVBA")
        .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("WorksheetsVBA").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("WorksheetsVBA").Protect Password:="1234", UserInterFaceOnly:=True
    Application.ScreenUpdating = True
End Sub

Vragen / suggesties

Hopelijk heeft dit artikel geholpen om meer inzicht te krijgen in valkuilen die er kunnen zijn bij het instellen van voorwaardelijke opmaak met VBA. Als er verdere vragen over dit onderwerp zijn of suggesties voor verbetering, plaats dan een reactie hieronder.

arrow_up