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 zal ik demonstreren hoe dat werkt.

 

Klassemodule clsCellColorChange aanmaken

Het nieuwe CellColorChange-event zal worden afgehandeld door een klasse-module. 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 clsCellColorChange. In deze klassemodule moet de volgende code gezet worden:

Private WithEvents CmdBar As Office.CommandBars
Private objWks As Worksheet, bolAllCellsViewed As Boolean
Private varCurColor() As Variant, varPrevColor() As Variant, _
    strSelectionAddress As String

Private Sub Class_Initialize()
    bolAllCellsViewed = False
End Sub

Public Sub SetActiveWorksheet(wks As Worksheet)
    Set objWks = 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 strSelectionAddress <> rngCurSelection.Address Then
        Erase varCurColor
        Erase varPrevColor
        strSelectionAddress = ""
        bolAllCellsViewed = False
    End If
    On Error Resume Next
    For Each rngCell In rngCurSelection
        ReDim Preserve varCurColor(i)
        varCurColor(i) = rngCell.Interior.Color
        If varPrevColor(i) <> varCurColor(i) Then
            If bolAllCellsViewed Then
                varPrevColor(i) = varCurColor(i)
                CallByName objWks, "CellColorChange", VbMethod, rngCell
            End If
        End If
        i = i + 1
        If i >= rngCurSelection.Cells.Count Then
            bolAllCellsViewed = True
            ReDim Preserve varPrevColor(UBound(varCurColor))
            varPrevColor = varCurColor
        End If
    Next
    On Error GoTo 0
    strSelectionAddress = rngCurSelection.Address
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 objCellColorEvent As clsCellColorChange

Private Sub Workbook_Open()
    Set objCellColorEvent = New clsCellColorChange
    objCellColorEvent.SetActiveWorksheet ActiveSheet
End Sub

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

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

Private Sub Workbook_Deactivate()
    Set objCellColorEvent = 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
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 class 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 laat ik hier buiten beschouwing.
  • Het principe van dit CellColorChange-event kan gebruikt worden om andere eigen events te maken.

Geef een reactie

Het e-mailadres wordt niet gepubliceerd.