Excel user-defined function not called on cell update

Question

I have a user-defined function written in VBA that updates colors in a drawn shape (traffic light consisting of three circles). The call in a worksheet cell looks somehow like this:

setTrafficLight(A1, "Oval 1", "Oval 2", "Oval 3")

where A1 is a cell containing e.g. "green" or "red". The other parameters are the names of the shapes.

I had a problem that the function was called and deleted the undo history (calling a user-defined function in Excel does disable older undo entries). To fix this, I registered an empty undo function via

Application.OnUndo "Undo SetTrafficLight", "undoSetTrafficLight"

In my setTrafficLight function, Application.Volatile is set to False so that the function does not get called every time any value on the sheets changes. But now the function isn't even called when the input value in A1 changes. With Application.Volatile = True it works, but then undo is not working properly. If I completely remove Application.Volatile then the updating works correctly, but only one level of undo can be used.

Any idea on how to fix this problem?

Answer 1

How about using Worksheet_Change event?

something like this:

this should work for A1 cell, but could be easily adjusted for others

Private Sub Worksheet_Change(ByVal Target As Range)
   If Target.Row = 1 And Target.Column = 1 Then
      Call setTrafficLight
   End If
End Sub