使用VBA跟踪Excel 2007/2010中的样式更改 [英] To track style changes in Excel 2007/2010 using VBA

问题描述

我需要跟踪某些工作表中的单元格样式更改。我无法在Excel 2007/2010中使用buid-in跟踪，因为我需要自定义某些东西。我尝试通过Workbook_SheetChange跟踪样式更改，但失败。当我将单元格从一个样式更改为另一个样式时，它不会触发。

I need to track the cell style changes in some sheets. I cannot use the buid-in tracking in Excel 2007/2010 because I need to customize something. I tried to track the style change by Workbook_SheetChange but failed. It never fire when I change the cell from one style to another.

是否还有其他可用于跟踪样式更改的事件？或者任何解决方法？

Is there any other event that can be used to track the style change? Or any workaround for that?

推荐答案

格式更改没有触发事件。

There is no event triggered on format change.

最好的解决方法是监视Worksheet_SelectionChange事件。当用户单击单元格时，必须存储对单元格的引用以及要监视的所有格式信息。下一次事件触发时，您必须回顾他们点击的最后一个单元格，将其当前格式与保存的格式信息进行比较，这将允许您检测更改。

The best workaround is to monitor the Worksheet_SelectionChange event. When the user clicks a cell, you have to store a reference to the cell, and all the format information you want to monitor for. Next time the event fires, you have to look back at the last cell they clicked, compare it's current format to your saved format information, and that will allow you to detect changes.

缺点是您只能在从格式化的单元格中单击后才能检测到更改。

The downside is you can only detect the change after they have clicked away from the cell they formatted.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Static LastRange As Range 'The last range selected'
    'For example, monitor the background color or the cell'
    Static LastColorIndex As Integer

    If LastRange.Cells(1).Interior.ColorIndex <> LastColorIndex Then
        'Do what you do'
    End If

    Set LastRange = Target
    LastColorIndex = Target.Interior.ColorIndex
End Sub

这是最简单的可能情况。如果它们一次性修改整个单元格区域，事情会变得更加复杂。

This is the simplest possible case. Things get more complicated if they modify an entire range of cells at once.

这篇关于使用VBA跟踪Excel 2007/2010中的样式更改的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！