excelvbacharts

Excel VBA chart data label position property update delay after chart layout change

I have an Excel chart like this:

enter image description here

And the following VBA macro which is toggling the HasLegend property and checking the Left position of the first DataLabel several times. When the legend is removed the chart stretches to fill the space and all the data labels move to the right a bit.

Sub Button1_Click()

    Debug.Print CStr(ActiveSheet.ChartObjects("Chart 1").Chart.SeriesCollection(1).DataLabels(1).Left)
    ActiveSheet.ChartObjects("Chart 1").Chart.HasLegend = False
    
    Debug.Print CStr(ActiveSheet.ChartObjects("Chart 1").Chart.SeriesCollection(1).DataLabels(1).Left)
    DoEvents
    Debug.Print CStr(ActiveSheet.ChartObjects("Chart 1").Chart.SeriesCollection(1).DataLabels(1).Left)
    DoEvents
    Debug.Print CStr(ActiveSheet.ChartObjects("Chart 1").Chart.SeriesCollection(1).DataLabels(1).Left)
    DoEvents
    Debug.Print CStr(ActiveSheet.ChartObjects("Chart 1").Chart.SeriesCollection(1).DataLabels(1).Left)
    DoEvents
    Debug.Print CStr(ActiveSheet.ChartObjects("Chart 1").Chart.SeriesCollection(1).DataLabels(1).Left)
    DoEvents
    Debug.Print CStr(ActiveSheet.ChartObjects("Chart 1").Chart.SeriesCollection(1).DataLabels(1).Left)
    DoEvents
    Debug.Print CStr(ActiveSheet.ChartObjects("Chart 1").Chart.SeriesCollection(1).DataLabels(1).Left)
    DoEvents
    
    ActiveSheet.ChartObjects("Chart 1").Chart.HasLegend = True

End Sub

As you can see in the Immediate window below, after removing the legend there is a delay before the DataLabel Left value is updated.

Microsoft Visual Basic for Applications

After the Legend is removed, the original position value is printed another 4 times before the new value is read from the Left property. How can I program this in a reliable way so that the code blocks until the value is updated without adding some random number of DoEvents, or adding a timer loop for some random timespan, and hoping for the best?

Example workbook: https://www.dropbox.com/scl/fi/3b96fim1lkxz1mh5i4pq4/test.xlsm?rlkey=ivdghw3xr87b99ss1avkqgi79&dl=1

Solution

  • Nothing can be done instantly...

    In order to wait until left position is updated, please try the next adapted code:

    Sub Button1_Click()
    Dim prevPos As Double, actChart As ChartObject
    
    Set actChart = ActiveSheet.ChartObjects("Chart 1") ' just to make following code lines shorter...
    prevPos = actChart.Chart.SeriesCollection(1).DataLabels(1).Left
    Debug.Print prevPos
    
    actChart.Chart.HasLegend = False
    Do While actChart.Chart.SeriesCollection(1).DataLabels(1).Left = prevPos
        DoEvents
    Loop
    
    'do whatever you need here...
    
    Debug.Print actChart.Chart.SeriesCollection(1).DataLabels(1).Left
    actChart.Chart.HasLegend = True
End Sub

    The above solution should be also adapted for the case you try it again **without setting .HasLegend = True... One way would be to also use a Global boolean variable (boolHasLeg as Boolean) which to be changed each time according to HasLegend status. And, of course, conditioning the loop using this variable, too.

    Otherwise, the code will be stuck in the loop...