我们可以在Excel中使用很多事件，例如Workbook_Open事件、Worksheet_SelectionChange事件等，这些都是Excel自带的标准事件，然而，有时候，我们希望在做某些操作时，也能够触发事件，例如当单元格的背景颜色改变时会触发事件，但Excel自身没有这样的事件，此时，可以为此自己定义一个单独的事件。在VBE中，插入一个类模块，将其重命名为clCellColorChange，输入下面的代码：Private WithEvents CmdBar As Office.CommandBarsPrivate oWks As Worksheet, bAllCellsViewed As BooleanPrivate vCurColor() As Variant, vPrevColor() As Variant, sSelectionAddress As String
Public Sub SetActiveWorksheet(wks As Worksheet) Set oWks = wks Set CmdBar = Application.CommandBarsEnd 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.AddressEnd Sub
Private Sub Class_Terminate() Set CmdBar = NothingEnd Sub
这个类模块是事件的核心。它使用了Commandbars的OnUpdate事件，每次触发此事件时，它都会检查所选单元格中的单元格颜色是否发生了变化，如果发生了变化，将调用相应工作表的CellColorChange事件。在VBE中，双击工程管理器中的ThisWorkbook打开其代码窗口，在其中输入下面的代码：Private oCellColorEvent As clCellColorChange
Private Sub Workbook_Open() Set oCellColorEvent = New clCellColorChange oCellColorEvent.SetActiveWorksheet ActiveSheetEnd Sub
Private Sub Workbook_SheetActivate(ByVal Sh As Object) Set oCellColorEvent = New clCellColorChange oCellColorEvent.SetActiveWorksheetActiveSheetEnd SubPrivate SubWorkbook_SheetDeactivate(ByVal Sh As Object) Set oCellColorEvent = NothingEnd SubPrivate SubWorkbook_Deactivate() Set oCellColorEvent = NothingEnd Sub
为了能够在相关工作表中使用该事件，必须在所有相应工作表代码模块中输入下面的代码：Public Sub CellColorChange(TargetRange As Range) '可在此放置自己的代码 MsgBox ActiveSheet.Name & "!" & TargetRange.Address & " 的颜色被改变!"End Sub
注：本文学习整理自worksheetsvba.com，供有兴趣的朋友参考。欢迎在下面留言，完善本文内容，让更多的人学到更完美的知识。
欢迎到知识星球：完美Excel社群，进行技术交流和提问，获取更多电子资料，并通过社群加入专门的微信讨论群，更方便交流。
