Dear All,

Excel 2000.

I have a UDF that looks like this (many thanks to Bob Phillips):

Function Function_Name(Code As Integer, Job As String, Country As String)
Dim sFormula As String

Application.Volatile
sFormula = "SumProduct(--(Rng1=" & Code & ")," & _
"--(Rng2=""" & Job & """)," & _
"--(Rng3=""" & Country & """), (Rng4))"
Function_Name = Evaluate(sFormula)

End Function

In several worksheets in the same workbook I have put a Worksheet Change
Event to display "Not updated". The code look like this:

Private Sub Worksheet_Change(ByVal Target As Range)
[Indicator].Value = "Not Updated"
End Sub

In the worksheets where I have the Worksheet Change Event I have dropdown
boxes (Validation - List) to ensure correct data entry.

Now... when I put Application.Volatile into the UDF, the UDF recalculates
automatically but the Worksheet Change Event is not executed when using the
dropdown boxes. Why?

If I remove Application.Volatile the Worksheet Change Event is executed
using the dropdown boxes but the UDF does not recalculate (as expected).

Talk about Catch22. Is there a piece of code I can add in the Worksheet
Change Event and/or the UDF to get them both to work properly?

Help much appreciated.
--
Regards,

Martin