VBA chart won't display while MSGBOX is active

Question

Hi My Problem is identical to this unanswered question from a while ago

MsgBox caused charts to appear as blank

Basically I'm doing some quality control in excel and trying to utilise VBA to make it easier, my code runs through a column until it finds the next chunk of flagged data, it then creates a chart of that and surrounding data for visual inspection, I then added an error box for the user to input whether they believe the error flags for that data should be deleted or not, however the chart wont actually display or render until the user interacts with the msgbox, since the chart can be viewed in the background I was hoping the user could just use this macro, quickly view the chart, say yes or no, and the error flags are either deleted or not, and the script moves on to the next flagged series of data. First time here, please feel free to let me know if any of this is not in the right format. Code below:

Dim SelRange As Range
Dim ExtRange As Range
Dim cht As Object
Dim errorflag As String



Selection.End(xlDown).Select

errorflag = ActiveCell

Range(Selection, Selection.End(xlDown)).Select
Selection.Offset(0, -1).Select

Set SelRange = Selection

Set ExtRange = SelRange.Offset(-SelRange.Rows.Count).Resize(3 * SelRange.Rows.Count)

ExtRange.Select

Set cht = ActiveSheet.Shapes.AddChart2
cht.Chart.SetSourceData Source:=ExtRange
cht.Chart.ChartType = xlXYScatterSmoothNoMarkers
cht.Chart.ChartTitle.Text = errorflag & " error flag of hydrograph of " & Cells(1, ActiveCell.Column).Value



MsgBox "Do you wish to delete the selected error flags?", vbYesNo

If Result = vbYes Then
SelRange.Clear

End If

I have tried as separate subs but then I can't delete the variables I want as they are declared in the previous sub.

I've tried adding pauses.

I've tried adding a second intermediary msgbox.

Answer 1

You need to allow Excel to process the Chart after you create it and before the message box. The function to achieve that is DoEvents

In this case (and some others), you need to call DoEvents twice.

Demo

Sub Demo()
    Dim rng As Range
    Dim cht As Shape
    Set rng = [c4:d8]
    Set cht = ActiveSheet.Shapes.AddChart2
    cht.Chart.SetSourceData Source:=rng
    cht.Chart.ChartType = xlXYScatterSmoothNoMarkers
    
    DoEvents
    DoEvents
    
    MsgBox "Hello"
End Sub