Chart will not update using Macro

[aboons]

Hi all,

I have a chart that is linked to dataset B (20 rows, 4 columns), that is in turn linked to the larger dataset A (252 rows). I have written a simple macro (see below) that increases the topleft value of dataset B with 1, which has an effect similar to scrolling through dataset A (if that makes sense). The effect would be a dynamic chart, showing the movement of the data in dataset A.

When I manually increase the topleft value of dataset B, the values are updated and the chart also updates. However, when I try doing it using the macro, the chart will not update. Note that I have added a 'deceleration' mechanism to the code as well.

Any ideas?

Thanks in advance.


Albert

Code snip:
For x = 1 To 252

'Decelerator
For y = 1 To 5000000
Next y

Range("g2").Value = Range("g2") + 1

Application.Calculate

Next x

 

[mole999]

do you need to force calculate 252 times, could you do that once after the loop, and maybe work in a refreshall

 

[aboons]

Hi,

No, not necessarily. That was just an experiment to see if it would work then. But it doesn't with or without.

 

[Yongle]

Try this method
Place in a NEW standard module and amend sheet name if required
Test by running macro Test

Private cel As Range

Sub test()
    Set cel = Sheets("[COLOR=#b22222]Sheet1[/COLOR]").Range("G2")
    cel.Value = 0
    Call Delay
End Sub

Private Sub Update_Chart()
    If cel > 252 Then Exit Sub
    cel = cel + 1
    Call Delay
End Sub

Private Sub Delay()
    Application.OnTime Now + TimeValue("00:00:01"), "Update_Chart"
End Sub

 

[aboons]

That works. Thanks! Would you care to explain why my method did not work? (but don't bother if you think it's not worth it)

 

[Yongle]

Would you care to explain why my method did not work? (but don't bother if you think it's not worth it)

I do not pretend to understand what goes on under the Microsoft hood, but I have observed that objects like charts etc do not appear to refresh until a procedure ENDS (regardless of anything I have tried)
Your procedure ends ONCE and the chart is then refreshed
With my method, the procedure is called repeatedly, ends after each increment thus allowing the chart to be refreshed