excel chart not updating when data changed by vba

Question

I have a Bubble chart that I am trying to add "motion" to by having a macro update the table and subsequently the chart. I have a cell that I use as the "offset" which is used by my data table to get the data. I have a button that runs a VBA macro that updates this "offset" cell for each month in my data, which updates the data table when the offset updates.

When I alter the offset cell manually, both the table and chart update. However, when I click the button and run the VBA macro, only the table updates, NOT the graph.

I have looked researched possible solution, including those items located here on Stack Overflow, and have tried the following:

-DoEvents
-Applications.Calculate
-ActiveWorkbook.RefreshAll
-Chart.Refresh
-setting the Application.ScreenUpdating to false and back to true

Here is my VBA code:

Sub Button7_Click()
Dim i As Integer

For i = 0 To 9:
    Range("P1").Value = i
    Application.Calculate
    Application.Wait DateAdd("s", 1, Now)
Next
Range("P1").Value = 0
End Sub

It shouldn't be this hard to update a graph when the table updates via VBA.

Welcome to SO! Is this a pivot chart or standard issue DIY chart? — urdearboy
– urdearboy, Commented Aug 21, 2018 at 23:31
When I run this (Win Excel 2013) the chart updates during the run. — Tim Williams
– Tim Williams, Commented Aug 21, 2018 at 23:35
@urdearboy This is a normal DIY chart. I am not using a pivot table. I also tried adjusting the wait time. I have tried 3 seconds, 5 seconds, and 30 seconds, and the graph never updates itself. — Rebecca Hendricks
– Rebecca Hendricks, Commented Aug 21, 2018 at 23:50
@TimWilliams I am using Excel 2016 and the VBA code I provided doesn't update the graph. — Rebecca Hendricks
– Rebecca Hendricks, Commented Aug 21, 2018 at 23:51
There are 34 solutions on this post. Hopefully one of them will help you — urdearboy
– urdearboy, Commented Aug 22, 2018 at 0:14

RAM · Accepted Answer · 2020-02-25 14:08:45Z

1

I've had this problem. Seems related with Excel 2016.

Sub CommButton1_Click()
i = Cells(4, 24)
start = Cells(4, 22)
rango = Cells(4, 23) + start
Do Until start > rango
    Sleep (20)
    Cells(4, 25).Value = start
    start = start + i
    DoEvents
    DoEvents
Loop
End Sub

The soultion seems to be to add another DoEvents. I've tried in Excel 2016 and it worked, but with a big time delay.

answered Feb 25, 2020 at 14:08

RAM

113 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Seiya Su · Accepted Answer · 2018-08-22 11:21:37Z

0

Based on my test, the following code works on my side:

Sub Button7_Click()
Dim i As Integer
Dim sheet As Worksheet
Set sheet = Worksheets("Sheet10")

ActiveSheet.EnableCalculation = True
Application.ScreenUpdating = True
For i = 1 To 9
   'sheet.Range("A1") = CStr(i)
   sheet.Cells(i, 1) = CStr(i)
    'Application.Wait (DateAdd("s", 1, Now))
Next

sheet.UsedRange.Calculate

ActiveSheet.EnableCalculation = False
Range("A1").Value = 0
End Sub

answered Aug 22, 2018 at 11:21

Seiya Su

1,8741 gold badge9 silver badges10 bronze badges

Collectives™ on Stack Overflow

excel chart not updating when data changed by vba

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related