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
 

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.
do you need to force calculate 252 times, could you do that once after the loop, and maybe work in a refreshall
 
Upvote 0
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:
Upvote 0
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:
Upvote 0
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)
 
Upvote 0
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:
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,255
Members
448,556
Latest member
peterhess2002

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top