CODEWITHSUNDEEP
excelvba
Tony Qu
Tony Qu

Reputation: 756

To track style changes in Excel 2007/2010 using VBA

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?

Upvotes: 0

Views: 1448

Answers (1)

Alain
Alain

Reputation: 27220

There is no event triggered on format change.

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.

Upvotes: 2

Related Questions