UDFunction triggering on cell refresh vs change in cell value

[chris1983]

I have created a UDF to display "Success" (to keep this simple) every time one of the cells in the range D1:D4 changes.

However, 3 out of 4 of the cells contain real-time data that is refreshed every 1500 milli-secs and so every time the cells refresh the function triggers the MsgBox to display even though the values haven't changed. I only want the MsgBox to display if the values in those cells change.... can anyone help, I have spent the last 3 days trolling the net for a resolution but to no avail?

=Trigger(D1:D4)

Function Trigger (theParameter As Variant)
MsgBox "Success"
End Function

[mikerickson]

Perhaps something like

Function Trigger(theParameter As Variant)
Static oneVal as Variant
Static twoVal as Variant
Static threeVal as Variant

If oneVal <> theParameter.Cells(1,1).Value _
Or twoVal <> theParameter.Cells(2,1).Value _
Or threeVal <> theParameter.Cells(3,1).Value
MsgBox "success"
End if
oneVal = theParameter.Cells(1,1).Value
twoVal = theParameter.Cells(2,1).Value
threeVal = theParameter.Cells(3,1).Value
End Function

[chris1983]

Thanks for the response. I'll be using this function on over 500 different lines in a single column so I can't cache or store the values to compare the set of values to. I was curious so I tried your code anyway on a single line and I was still getting the recurring MsgBox from the data refresh.

Any other suggestions?

[mikerickson]

What formula are you using for testing. That worked for me with =Trigger(A1:A4). If your cells are in a row the .Cells arguments have to be adjusted.

[chris1983]

I apologize if I wasn't clear, hopefully this helps...

I have the same function name on each row that will read in four fields of data on the corresponding row if any of the values change. Ie. =Trigger (D3:G3), =Trigger(D4:G4), =Trigger(D5:G5), etc.


example: www . designcymru. com/images/ example.jpg

If a field on row 3 changes the function will pull in the values of those fields for me to do something specific with them. If I copy the values from row 3 to compare them next time I get a change and then a value in row 5 changes it will pull in a seperate set of data and of course it will be different. If I somehow store the data by each line to compare against the next time it will be very resource intensive on the workbook given I need this function to cover 1000+ lines.

My real issue is initiating the function on a row when one of the corresponding cells actually changes value.... instead of the functon triggering across 1000 rows every 1500 millisecs when my real time data refreshes.

Hope this helps...