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

Following example code is working for me in Excel 2016

'Code edited since initial Posting. (I initially posted the incorrect code for the particular screen shot)

Sub Macro2()

    Dim cht_1 As ChartObject

    Set cht_1 = ActiveSheet.ChartObjects("Chart 1")

    cht_1.Chart.FullSeriesCollection(1).DataLabels(1).Format.TextFrame2.TextRange.Font.Size = 20

    cht_1.Chart.FullSeriesCollection(1).DataLabels(6).Format.TextFrame2.TextRange.Font.Size = 20

    cht_1.Chart.FullSeriesCollection(1).DataLabels(7).Format.TextFrame2.TextRange.Font.Size = 20

End Sub

Answer 2

OssieMac, first and foremost thank you for taking the time to review and reply. If you are ever in Atlanta GA, USA, I owe you a beverage of your choice!

There does seem to be a difference between what you and I are seeing (perhaps based on Excel version? patches? x32 vs x64?)

I opened a new file, used the same data, and ran your sample code. Firstly, I notice that the 'hello' label shows for you (wrapped), whereas it doesn't for me. In addition, when I run your sample code I don't get any errors, but the font sizes don't change. See screenshot below; the whole datalabels series is selected, although that doesn't show in a screen capture. 

I'm using MS Excel for Office 365 MSO (16.0.12228.20322) 64-bit

If we are both using Excel v16, and if you are also using x64 then I'd love to know which sub-version you are on. Also, if you have any additional suggestions for troubleshooting :)

The final work product is something I'm trying to use for myself at work, but I also already have permission from my company to share it freely with other folks in my industry... so I'm worried that others users might run into this too...   8-(

 

Answer 3

Hi again KERATL,

OK. I think I have had a win. Will be interesting to see if works in 365 / 64 bit because my Excel is in Office 2016 stand alone version (all updates).

My previous testing was done after I had manually altered the font sizes in the data labels and it worked. However, after saving and re-opening the workbook, it no longer worked until I manually changed the font sizes on the Data Labels again and same thing again after Saving, Closing and Re-Opening.

Anyway I think that I have found a workaround so give it a try.

Following procedure:

1. Click any cell on the worksheet to clear all selections on the chart.
2. Right Click a Data Label to select ALL Data Labels and display a DropDown of options.
3. Select "Font" option.
4. Ensure "Font" Tab is selected at top of dialog.
5. Check the box against "Equalize character height".
6. Not essential but suggest also check "Small caps".
7. Click OK
8. Now test

See my example workbook which I uploaded as a zipped file at the following link on OneDrive.

• Download and unzip the file.
• Above instructions on the worksheet.
• The VBA code is event code in Sheet1 module.
• Changing font size in cell K2 calls the event code.

https://1drv.ms/u/s!ArAXPS2RpafCrUjuBysGniylbXu-?e=Ni9Nsj

Below is a copy of the Change Event code. It actually changes all Data Labels on the chart but if you only want to change some then simply edit out the ones not required.

Feel free to get back to me again because I am interested in your results with 64 bit on 365.

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim dblFontSize As Double       'Font size can have decimal places and hence declare as double

    Dim cht_1 As ChartObject

    If Target.Address <> "$K$2" Then Exit Sub       'Only run this code if cell K2 Changed

    dblFontSize = Target.Value

    Set cht_1 = Worksheets("Sheet1").ChartObjects("Chart 1")

    With cht_1.Chart.FullSeriesCollection(1)

        .DataLabels(1).Format.TextFrame2.TextRange.Font.Size = dblFontSize

        .DataLabels(2).Format.TextFrame2.TextRange.Font.Size = dblFontSize

        .DataLabels(3).Format.TextFrame2.TextRange.Font.Size = dblFontSize

        .DataLabels(4).Format.TextFrame2.TextRange.Font.Size = dblFontSize

        .DataLabels(5).Format.TextFrame2.TextRange.Font.Size = dblFontSize

        .DataLabels(6).Format.TextFrame2.TextRange.Font.Size = dblFontSize

        .DataLabels(7).Format.TextFrame2.TextRange.Font.Size = dblFontSize

        .DataLabels(8).Format.TextFrame2.TextRange.Font.Size = dblFontSize

    End With

End Sub

Answer 4

My apologies for the delayed follow-up (last week was all 12-hour days for me, and not on this project).

Caveat- I don't fully understand how the datalabels series [object model] works; I would have guessed that the code in OssieMac's last post would manipulate each label size individually, but (at least in x64) that doesn't seem to be the case.

Observation: if the fontsize is large enough (at any point, including through my VBA manipulations), the 8th label is the first to be auto-hidden by Excel. After that point, .DataLabels(8). throws an error, so I wrapped all 8 datalabels lines in On Error Resume Next / On Error Goto 0. However, in reality I'll still need to assign smaller font sizes to the outer layers of the graph to try to get as many labels showing as possible, and I don't yet know how to force a hidden label to show again in the smaller font, if I can't apply the smaller font to an (auto)hidden label.

Testing of the code graciously provided by OssieMac above, Excel x64 applies the fontsize change to the entire datalabels series, independent of the datalabel ID (1-8). To test if I could affect individual labels, I added *2 or /2 after dblFontSize on just some rows of the code. The desired/expected impact upon entering 10 in K2 would have resulted in the some datalabel having fontsize=10, and others at 5pt or 20pt. In testing, each line of code affects the whole label series (at least in Excel x64). If the last executable row is *2, then the entire datalabel series ends up at 20pt. If the last executable row is /2, then the entire series of labels is 5pt

Observation: label 8 visibility (e.g., auto-hide by Excel) is based on more than font size. As I increment through font sizes during testing, the label is visible at font=14, invisible at font=15, then visible again at font=16, 17, 18 and invisible again at 19 and above. No idea why this isn't linear :(

Observation: During testing the font size vs. visibility, when I got to a font size in the mid-20s it was large enough that /all/ the labels were (auto)hidden - from that point forward, the code had no effect, as it seems to only affect visible labels (invisible labels without the OnErrorGotoNext result in an RTE 424)

When I delete and recreate the chart, it resets the environment (requires the user to right-click and select font size per OssieMac's suggestion). In my use case, users will be pasting in different raw data sets, and my existing VBA deletes any pre-existing charts on the target page, and creates a new chart (and wedge colors) based on the new raw data (which may include different number of layers, etc.). While I could rewrite the code to just update the graph source (source range size, etc.), at this point (given the other issues above) I'm now thinking that moving to Excel x32 might be the better (or only) solution to all my problems, and I can just tell my professional group that if they want to use the tool, they are also stuck with x32  ... I just have to figure out when I'll have time to do the full uninstall/reinstall and reapply all my personal settings to MS office...