locked
Object model question: Sunburst chart datalabels and/or chart.refresh RRS feed

  • Question

  • Hi all - posting here because I think this is question is more about the object model and appropriate calls to update the graph, rather than a general usage question.

    When I create a Sunburst chart, I can select the data labels and manually increase/decrease font size via the ribbon. 

    When I try to do the same in VBA, it seems to be 80% similar, in that the font size displayed on the ribbon changes *but* the actual font displayed on the chart does not change.

    * VBA confirms the correct font size has been assigned

    * a subsequent tap on the ribbon font size widget will force the graph to redraw the font size at the newly designated size (e.g. if the original size was 12 and my VBA sets it to 6, tapping the "Decrease Font Size" button on the ribbon will change the font size to 5 in both the ribbon font size display /and/ on the live graph 

    *DoEvents and chart.refresh do not force the graph to redraw the font size (in the absence of the user tapping the "decrease font size" button)

    So...

    Are there any other methods I can call in VBA to force the sunburst chart to redraw including the newly assigned datalabel font size?

    Eventually I'll want to set the font size for each label individually, instead of the entire datalabels collection. I don't know if doing it that way might solve my overall problem, but I welcome code snippets that would help me test (even getting to this point was lots of trial and error)

    My last post in this thread: https://answers.microsoft.com/en-us/msoffice/forum/msoffice_excel-msoffice_custom-msoversion_other/vba-syntax-to-edit-font-size-of-chart-labels/593edb84-e4a6-4f7c-bc90-0646818edc5d?tm=1577714294493

    gives a quick & easy data set and instructions for how to build a simplified version of what I'm trying to accomplish, for testing purposes. Generic code follows:

    ActiveSheet.ChartObjects(1).Activate

    ActiveChart.SeriesCollection(1).Select

           With ActiveChart.SeriesCollection(1)
                Verify_Series_Is_Valid = .HasDataLabels
                .DataLabels.Select
                .DataLabels.Format.TextFrame2.TextRange.Font.Bold = True
                .DataLabels.Format.TextFrame2.TextRange.Font.Size = 6

                 debug.print .DataLabels.Format.TextFrame2.TextRange.Font.Size

          End With

    DoEvents

    ActiveChart.refresh

    Thank you!


    Tuesday, December 31, 2019 1:50 PM

All replies

  • Hi,

    Since this forum focuses more on questions related to Office desktop applications, if you need further assistance on this problem, I will help you move the thread to the following forum: Excel for Developers forum on MSDN. You may get more helpful replies there.

    The reason why we recommend posting appropriately is you will get the most qualified pool of respondents, and other partners who read the forums regularly can either share their knowledge or learn from your interaction with us.

    Thanks for your kind understanding.

    Best Regards,

    Herb


    Please remember to mark the replies as answers if they helped. If you have feedback for TechNet Subscriber Support, contact tnsf@microsoft.com.

    Click here to learn more. Visit the dedicated forum to share, explore and talk to experts about Microsoft Office 2019.

    Wednesday, January 1, 2020 2:57 AM