can't get calculate event to fire vba

[Cicada]

hi all,

I have the following code.

Please Login or Register  to view this content.

HILO_TURBO has data entered into it from an external dde link that updates every 10 seconds or so and i have formulas that calculate the above mentioned data. The values in J19:J22 recalculate all the time but i would like to capture them in D3:D6 each time the above criteria is satisfied. this criteria is satisfied every so often and so the old values contained in D3:D6 i want to be replaced with the newly calculated values. I hope this clear. when i enter the code in visual basic it performs the above macro once the above conditions are satisfied, but when the conditions occur again it does not replace the old values with the new ones as desired. I have to press run (in visual basic) again while these conditions are active or present for the macro to run again. anyone have any ideas as to why this is happening. Change event will not work with a dde link and i cant see why calculate event will trigger it once and not again. thanks in advance for your help.

Cicada

Please Login or Register  to view this content.

[shg]

Please edit your post to add code tags.

Thanks.

[tony h]

Hi cicada (please amend the code tags)

You don't say which sheet you have the worksheet_calculate attached to. It will only fire on an actual calculate from a formula on the sheet to which it is attached. I am pretty sure it won't fire from just new data being pasted from a DDE link.

If you arn't sure when it is being fired use a debug.print or msgbox in the event just to let you know.

[Cicada]

thanks for the reply tony. In HILO_TURBO There are tables filled with data pasted directly from a dde link next to these tables in other ranges on HILO_TURBO i have created formulas
that calculate data from the tables. I thought this would have sufficed as a calculate event, though i concede i don't really know. The conditions in my code are from ranges that the dde link posts directly to does this matter. secondly i have put the code in worksheet 1 module that i am using simply to have the ranges copied to though no calculation takes place. The reason i put it there is because i thought it didnt matter, though ur right it does so i will try the other way. any further suggestions will be greatly appreciated. thanks again.

Cicada

[royUK]

To best describe or illustrate your problem you would be better off attaching a dummy workbook, the workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook.

If needed supply a before and after sheet in the workbook so the person helping you can see what you are trying to achieve.

Doing this will ensure you get the result you need!