G
Guest
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.
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.