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, Aug 21 '18 at 23:31
When I run this (Win Excel 2013) the chart updates during the run. — Tim Williams, Aug 21 '18 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, Aug 21 '18 at 23:50
@TimWilliams I am using Excel 2016 and the VBA code I provided doesn't update the graph. — Rebecca Hendricks, Aug 21 '18 at 23:51
There are 34 solutions on [this](https://stackoverflow.com/questions/4902703/excel-chart-wont-update) post. Hopefully one of them will help you — urdearboy, Aug 22 '18 at 00:14
Maybe try using `Sleep` instead of `application.wait` https://stackoverflow.com/questions/42113581/cant-refresh-a-chart-in-excel-vba-excel-2016 — Tim Williams, Aug 22 '18 at 00:16
@TimWilliams I could get the code as is to run as expected on older excel but the exact same code doesn't update chart on 2016. Strange — urdearboy, Aug 22 '18 at 00:51

score 1 · Answer 1 · answered Feb 25 '20 at 14:08

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.

score 0 · Answer 2 · answered Aug 22 '18 at 11:21

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

excel chart not updating when data changed by vba

2 Answers2

Linked