View Full Version : UDFunction triggering on cell refresh vs change in cell value
chris1983
11-18-2011, 01:25 PM
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
11-18-2011, 01:50 PM
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
11-18-2011, 07:14 PM
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
11-18-2011, 07:53 PM
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
11-18-2011, 08:09 PM
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...
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.