Chart will not update using Macro

aboons

Board Regular
Joined
Mar 14, 2007
Messages
79
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
 

Some videos you may like

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.

mole999

Moderator
Joined
Oct 23, 2004
Messages
10,295
Office Version
2019, 2016, 2013
Platform
Windows
do you need to force calculate 252 times, could you do that once after the loop, and maybe work in a refreshall
 

aboons

Board Regular
Joined
Mar 14, 2007
Messages
79
Hi,

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

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
5,647
Office Version
365
Platform
Windows
Try this method
Place in a NEW standard module and amend sheet name if required
Test by running macro Test

Code:
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
 
Last edited:

aboons

Board Regular
Joined
Mar 14, 2007
Messages
79
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

Well-known Member
Joined
Mar 11, 2015
Messages
5,647
Office Version
365
Platform
Windows
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
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,095,726
Messages
5,446,153
Members
405,386
Latest member
xcookiemonster64

This Week's Hot Topics

Top