VBA maken van CellColorChange gebeurtenis

In Excel zijn behoorlijk veel standaard gebeurtenissen beschikbaar, zoals Workbook_Open en Worksheet_SelectionChange, maar het wijzigen van de achtergrondkleur van een cel triggert geen gebeurtenis. Dit kan soms wel gewenst zijn (bijvoorbeeld in het geval van het Overrulen van voorwaardelijke opmaak). Het is echter wel mogelijk om hiervoor een apart event te definiëren. In dit artikel wordt gedemonstreerd hoe dat werkt.

Klassemodule clCellColorChange aanmaken

Het nieuwe CellColorChange-event zal worden afgehandeld door een klassemodule. Hiervoor moet met behulp van de VBA-editor een nieuwe klassemodule worden aangemaakt (via Invoegen->Klassemodule). De naam van deze module moet daarna veranderd worden in clCellColorChange. In deze klassemodule moet de volgende code gezet worden:

'-----------------------------------------------------------------------------------------------------------------------------------------
' Auteur    : Manfred van den Noort
' Copyright : © 2020 worksheetsvba.com, alle rechten voorbehouden
' Versie    : 1.0
' Datum     : 2020-11-09
' Doel      : Maken van CellColorChange gebeurtenis
'-----------------------------------------------------------------------------------------------------------------------------------------

Private WithEvents CmdBar As Office.CommandBars
Private oWks As Worksheet, bAllCellsViewed As Boolean
Private vCurColor() As Variant, vPrevColor() As Variant, sSelectionAddress As String

Public Sub SetActiveWorksheet(wks As Worksheet)
    Set oWks = wks
    Set CmdBar = Application.CommandBars
End Sub

Private Sub CmdBar_OnUpdate()
    Dim rngCurSelection As Range, i As Long, rngCell As Range
    Set rngCurSelection = Selection
    If sSelectionAddress <> rngCurSelection.Address Then
        Erase vCurColor
        Erase vPrevColor
        sSelectionAddress = ""
        bAllCellsViewed = False
    End If
    On Error Resume Next
    For Each rngCell In rngCurSelection
        ReDim Preserve vCurColor(i)
        vCurColor(i) = rngCell.Interior.Color
        If bAllCellsViewed Then
            If vPrevColor(i) <> vCurColor(i) Then
                vPrevColor(i) = vCurColor(i)
                CallByName oWks, "CellColorChange", VbMethod, rngCell
            End If
        End If
        i = i + 1
        If i >= rngCurSelection.Cells.Count Then
            bAllCellsViewed = True
            ReDim Preserve vPrevColor(UBound(vCurColor))
            vPrevColor = vCurColor
        End If
    Next
    On Error GoTo 0
    sSelectionAddress = rngCurSelection.Address
End Sub

Private Sub Class_Terminate()
    Set CmdBar = Nothing
End Sub

Deze klassemodule vormt het hart van het event. Hierbij wordt gebruikt gemaakt van het OnUpdate-event van de Commandbars. Telkens als dit event wordt afgevuurd, wordt gecheckt of er in de geselecteerde cellen een celkleur is veranderd. Zo ja, dan wordt het CellColorChange-event van het betreffende worksheet aangeroepen.

Overige code

Alleen het aanmaken van deze klassemodule is natuurlijk nog niet genoeg. Om dit te laten werken moet deze klassemodule gekoppeld worden aan de betreffende worksheets. In dit voorbeeld ga ik ervan uit dat dit event gebruikt moet kunnen worden in alle worksheets van het betreffende bestand. Daarvoor is de volgende code nodig, die in ThisWorkbook geplaatst moet worden:

Private oCellColorEvent As clCellColorChange

Private Sub Workbook_Open()
    Set oCellColorEvent = New clCellColorChange
    oCellColorEvent.SetActiveWorksheet ActiveSheet
End Sub

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
    Set oCellColorEvent = New clCellColorChange
    oCellColorEvent.SetActiveWorksheet ActiveSheet
End Sub

Private Sub Workbook_SheetDeactivate(ByVal Sh As Object)
    Set oCellColorEvent = Nothing
End Sub

Private Sub Workbook_Deactivate()
    Set oCellColorEvent = Nothing
End Sub

Dus telkens wordt bij het selecteren van een sheet de klasse aan deze sheet gekoppeld. Om het event in de betreffende sheet te kunnen gebruiken moet de volgende code achter elk sheet geplaatst worden:

Public Sub CellColorChange(TargetRange As Range)
    'eigen code
    MsgBox "Cel kleur van " & ActiveSheet.Name & "!" & TargetRange.Address & " is gewijzigd!"
End Sub

Op de plek van de eigen code kan dan de code worden geplaatst die moet worden uitgevoerd bij CellColorChange-gebeurtenis.

Tenslotte

Nog een aantal opmerkingen tot slot:

• Deze code maakt gebruik van het OnUpdate-event van de CommandBars. Dit event wordt in de praktijk erg vaak afgevuurd en daarom wordt het CellColorChange-event erg vaak geëvalueerd. Dit kan bij grote bestanden vertragend werken.
• Voor elke gewijzigde cel in de selectie wordt de gebeurtenis gegenereerd. Dus als er meerdere cellen zijn gewijzigd, wordt het event ook meerdere keren afgevuurd.
• Bij deze code worden alle cellen van een selectie geëvalueerd. Bij erg grote selecties kan dat problemen geven. Zo nodig kan dat in de klasse worden afgevangen, door eerst het aantal cellen van de selectie te tellen en bij een te groot aantal het commando Exit Sub te laten volgen.
• Het event reageert niet op wijzigingen van de celkleur die worden aangebracht door middel het kopiëren met vulgreep. Deze kopieeractie triggert echter wel het Worksheet_Change-event en eventueel zou daar het event afgehandeld kunnen worden, maar dat blijft hier buiten beschouwing.
• Het principe van dit CellColorChange-event kan gebruikt worden om andere eigen events te maken.

Maken van CellColorChange gebeurtenis 1.0

Download