Updating/refreshing chart label font size

Question

Thanks to Andreas_Killer for replying on my earlier thread. I'm creating an updated post because my question is now sufficiently different from my original post, based on what I've learned so far.

First, since I am in a controlled IT environment and our firewall blocks GoogleDrive and other external file storage locations, I will endeavor to give the simplest steps to replicate my problem below. I am using Win10x64 and Officex64 (365 ProPlus local install of v1909), but would welcome input from anyone as my hope is to get this working across all PC versions of Excel that have the Sunburst chart 

The problem: even after the Sunburst label font size is changed by VBA, I have not found a successful way to force Excel to actually implement the change. Screen.updating is on, chart.refresh has no effect, etc. While the change appears to be accepted by excel (see steps below), the change is later ignored/forgotten once the datalabel collection loses focus.

Steps to create a simple test case:

Copy the following table into A1 of a new workbook;

A	B			1
A	C			1
A	D	E		1
A	D	F	G	1
A	D	F	Hello_There	1

Then with this range selected, insert chart/sunburst. You should get something like the graph below.  I intentionally made the "Hello_There" text longer so that Excel wouldn't show the label by default, because of some of my earlier lessons trying to loop in VBA - if the label isn't showing, that seems to affect which syntax works. Having some hidden labels is representative of my actual project graphs.

Once the graph is created, click once on a data bar (blue in my picture) to select the chartseries, then click on one of the labels to select the datalabels collection.

Open the VBE, and in the immediate window, paste the following and execute it:

ActiveChart.SeriesCollection(1).DataLabels(1).Format.TextFrame2.TextRange.Font.Size = 4

The font size indicator in the home ribbon will show 4pt font, but the visual display of the graph does not change for me.

No other VBA commands I've tried will refresh the graph with the 4pt setting.

Only a manual click on the increase/decrease font size buttons (while the datalabel series is still selected) seems to force the change.

If I click off the graph (into a worksheet cell) and then click back into the datalabels collection, the home ribbon font size box shows that the font size is now back to the original font size... ?

Thanks in advance for any ideas - I'm looking for any solution at all, even an inelegant one, as this has effectively stopped all progress on the reporting I was building.

Thank you!

Answer 1

You shouldn't be in too much of a hurry to change to 32bit. Try to access a computer with 32 bit installed and test first.

I have been doing some further testing and I think that I might have mis-led you re setting the font on the labels manually.

After creating a new chart, I have now found that for the VBA code to work reliably it is first necessary to select each label individually and then select Font and then click OK in the dialog. There is no need for the small caps or Equalize to be set. The important function is to just select the font option and then click OK for each individual label.

I have been attempting to do the above with VBA code but so far unsuccessful and I have had to perform the functi****f many to do and not practical if the chart is created with VBA code.)

Answer 2

I added allcaps in VBA to see if that alone would trigger the UI updating (allowing the font size changes);

    ActiveChart.FullSeriesC****s.Format.TextFrame2.TextRange.Font.Allcaps = True

    ActiveChart.FullSeriesC****).Format.TextFrame2.TextRange.Font.Size = 16

there must be some other hidden change via the manual use of the font dialogue, as this code does force the all caps, but doesn't enable the font size change. That's confirmed if you have successfully tested that just opening the f****ent to enable the font size changes. What is actually changing is definitely outside my wheelhouse :)