Format Change Event for Excel VBA

I'm trying to prevent people from modifying (most) of my spreadsheet while still alowing them to use the sort function of the AutoFilter. What I've done so far is used the Worksheet_Change event and Application.Undo to automatically undo any values entered into cells that I don't want to be modified. This works great, except that it can't detect changes in formatting. Does anyone know of a means to trigger an event when the format (text/background color) of a cell is changed?

Thanks!

The_Third

Posted 2010-08-06T20:13:10.380

Reputation: 21

You mean to say that you want to reverse the Cell Fill Colour? Did you try Cells.Interior.Pattern = xlNone or Selection.Interior.ColorIndex = xlNone – Rajesh S – 2018-05-30T10:28:50.583

You should try protecting with UserInterfaceOnly, and see if it allows AutoFilter to work. – Lance Roberts – 2010-11-01T20:32:21.353

Answers

The events you are limited to at the worksheet level are as follows:

Activate
BeforeDoubleClick
BeforeRightClick
Calculate
Change
Deactivate
FollowHyperLink
PivotTableUpdate
SelectionChange

I suppose you could try something at the SelectionChange event. If someone changes the formatting of a cell and then moves to a different cell that event will be called.

Whatever code you have in that area will constantly be called, so it would be a good idea to preface whatever you do with some quick checks to make sure the cells are in a certain range, have data, etc. Your sheet could get pretty sluggish otherwise as users move from cell to cell.

Michael

Posted 2010-08-06T20:13:10.380

Reputation: 1 185

Thanks for the help! What I ended up doing is preventing users from saving the sheet; anything written in the "Comments" column triggers the Worksheet Change event to save a time stamped version in another sheet. The time stamped comments are then transferred over once the original sheet is reopened. – The_Third – 2010-08-11T13:45:22.453

i found it pretty simple:

At ThisWorkbook:

Public Sub Workbook_Open()
Set SelectLast = Range("A1")
SelectVal = Range("A1").Value
SelectColor = Range("A1").Interior.Color
End Sub

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Excel.Range)
Dim LastVal As String
Dim LastColor As String
LastVal = ""
LastColor = ""
For i = 1 To SelectLast.Columns.Count
    For y = 1 To SelectLast.Rows.Count
        LastVal = LastVal & SelectLast.Cells(y, i).Value
        LastColor = LastColor & SelectLast.Interior.Color
    Next
Next
If ((LastVal <> SelectVal) Or (LastColor <> SelectColor)) Then MsgBox ("Something happend")
SelectVal = ""
SelectColor = ""
For i = 1 To Target.Columns.Count
    For y = 1 To Target.Rows.Count
        SelectVal = SelectVal & Target.Cells(y, i).Value
        SelectColor = SelectColor & Target.Interior.Color
    Next
Next
Set SelectLast = Target
End Sub

and at Module1

Global SelectLast As Excel.Range
Global SelectVal As String
Global SelectColor As String

It possible watch anything with slight modification.

nonUser

Posted 2010-08-06T20:13:10.380

Reputation: 11

Not sure why this was downvoted: it seems to me that 1) this event is triggered just in time to detect changes, and 2) that it reverts both the value and color (I am not too sure if this actually happens above?). Next, I assume the author is right that some other formatting things besides color could be reverted too? – Arjan – 2014-02-15T11:43:04.903

Hello, sorry for my english iam not really good in, but i found little bug when iam testing. With very big range its very slow. For my use its irrelevant, but it should be faster if you leave the loops, and let comparing just first left cell. But, sure you can use to revert any changes. – nonUser – 2014-02-15T17:22:22.537