The target range in my worksheet_change macro is comprised of formulas. The event will not run because the target range is formulas. Is there a way to re-write my macro so that the worksheet_change can recognize a formula?
Please Login or Register to view this content.
My understanding is that the worksheet change event monitors cells that are changed manually or by code ... not the effect on a cell with a formula.
I think you will need to monitor the cell(s) you are changing and then test whether the cell with the formula in it has been set to "TRUE"
Regards
I dont understand. The cell with the formula in it can either be TRUE or FALSE depending on the value in another cell. I am lost with your suggestion..to me it appears as though that is exactly how my sheet is set up already..
I'm not sure how to say this another way ..."the worksheet change event monitors cells that are changed *manually* or *by code* ... not the effect on a cell with a formula."
The cell you are changing is *not* in the Range("aa4:aa53") ... it is somewhere else on the worksheet but the cell showing true or false *is* in that range.
If you change the value in O59 to, say, 20%, the value in AA4 will change from TRUE to FALSE. However, it is not and will not be picked up in the change event. If you monitor the change in O59 to R81, you can then check to see if the equivalent cell in AA changes.
Please Login or Register to view this content.
Regards
Just reiterating TMShucks' point ... a value changing as a result of a formula calculation does not invoke the Change event only the Calculate event.
It follows that "technically" you can use the Calculate event, however, the important point is that there is no Target in a Calculate event so trapping the appropriate action can be very difficult (and most likely inefficient) given Calculation can be invoked for all manner of reasons.
You're nearly always better off working backwards as TMShucks says to find the "prime mover(s)" which when altered do invoke the Change event, coding accordingly.
All of the above is just echoing the points already made and outlining risk in using Calculate event.
My Recommended Reading:
Volatility
Sumproduct & Arrays
Pivot Intro
Email from XL - VBA & Outlook VBA
Function Dictionary & Function Translations
Dynamic Named Ranges
Thanks for the clear explanation guys it makes more sense now.
My spread sheet is going to have multiple ranges, not just rows 59:81 but 86:108 ect..(As can be seen in the attached workbook). Can you help me edit the code to montior these additional ranges with the assumption that more ranges will be added over time?
Also I am noticing the code that you just wrote is playing a sound whenever the value in O59 changes and AA4 is TRUE. (If I change the value from 4.00 to 4.01 to 4.02 the sound will play everytime) Is there a way to modify this to play just when AA4 changes from FALSE to TRUE?(When my conditional format in O59 changes to dark green) Maybe some other type of code can do this, different from the worksheet_change?
I know this is a lot to ask for but I am constructing this sheet for monitoring stock prices and it is very important to have sound alerts, in additional to the conditional format so that I do not miss any specific prices changes.
Here is the worksheet with the additional ranges
How are O59 etc being updated ? There are no formulae in these particular cells.
On an basic note - trapping DDE updates is pretty difficult I'm afraid - at least en masse it is - in my experience.
For a handful of DDE's you can use Static variables but for more than that it can get tricky or inefficient.
Hopefully some Trader Types are out there on the board who can offer you more specific help.
I confess I've never really got to the bottom of the best way to handle trapping DDE's in large quantities (if indeed that's the fundamental issue here)
O59 is a IF statement that is not linked to the actual DDE..
=IF(L59="",0,IF($C59="Long",L59-L81,L81-L59))
Being that this is a IF statement does it make things easier?
The formula has implicit DDE precedents - ie L59 is a precedent of O59 and J59 (a precedent of L59) is a DDE call by the looks of things.
I think you're going to be looking at using the Calculate event but to be perfectly honest I doubt I can resolve for you - above my pay grade I'm afraid - I don't have the requisite experience to know the best way to handle this type of thing.
The frequency with which the DDE's update can also have an impact - should they be refreshing faster than Excel can complete it's events.
I'm afraid I have to bow out here - hopefully someone else is up to it !
Thanks. Can anyone else help me out?
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks