Change of value in a cell don't trigger macro

Hello

I have been struggling with the problem of how to make a event based macro work.
Hope someone out there know how-to. I'm a beginner in VBA, and new to this forum.

Behind cell R47 is a formula which display a number if a condition is meet, or nothing if it isn't. In case the number is more than 1, a macro is supposed to kick off. The macro is doing a simple task of copying and pasting an area, and has the side effect that the number in R47 disappear, but only until data from the net result in another figure in R47,(and trigger the macro again) . The problem is only....nothing happens.
I will be happy if someone has suggestions. The code:

Please Login or Register  to view this content.

If R47 contains a formula then you need to use the Worksheet Calculate event rather than the Change event. Note that this will be triggered whenever any formula is recalculated.

Hello StephenR

On the actual sheet there's a lot of activity going on, so i hope not that any calculation will result in macro action.

Now i deleted the other code and swap it with this one. Though, nothing happening so far. Is it correct?:

Private Sub Worksheet_Calculate()
If Range("$R$47").Value > 1 Then

Macro1


End If
End Sub

Please use code tags as per forum rules.

That looks ok. Is the calculate code definitely in the right sheet module?

Yes, it is in the same sheet as the the mentioned cell "R47".Macro1, which i have recorded, is in a seperate module.

Post a workbook then. And add code tags!

Sorry about the tags. Unlike the new thread message box, this one has only quote tags and not code tags, but i guess the quote tags has to be used around code as well.
The workbook is not in english, so i don't know if it make sense, but here it is anyway..

The workbook!

It works for me if I press f9 to force calculation.

That's strange!

At my place, it's now either going crazy, repeating macro1 with full speed, or doing nothing. Also if i delete contents of cell AE28:AO62 - and this way forcing a figure in cell R47 is nothing going on.
The point is that the macro should happen by it self, if data from the internet makes condition right, so no manual action is needed. That's why i started out with the change event code.
I had to stop the macro pressing ctrl+Break. But then it will not start again!

Have you deleted any worksheet change code?

And is calculation set to automatic?

Yes, i deleted the the worksheet change event code, and exchanged it with the worksheet calculate code.
The calculation is set to automatic.

Sorry, I hadn't noticed that my calculation was set to manual and once I set it to automatic I experienced the problem you describe. Try adding at the start of Macro1

Please Login or Register  to view this content.

and turn it back on at the end.

Bravo, it's working

I have tried the enableevents sentence before,but i did put it together with the change event code, and not the macro itself. So it did the trick. I noticed however, that the macro is also triggered if any cell in the sheet is activated, so i wonder if its going to be alright, when i get data in from the internet. This is only a dummy sheet, so i can only check it for real tomorrow on the real sheet, in office opening hours. So far it's promising..Thank a lot..I keep the thread open until tomorrow, though.

I'm afraid it will be triggered any time any formula changes. There might be workarounds, but we would probably need more details of what you are trying to achieve.

Thank you stephenR

Yes it's either not working, or working a lot. I think it is getting to complicated to explain how it all are put together, so i was thinking of hiring someone to fix it for me. so i wouId like to close this thread,