VBA to get Chart Category Labels to Wrap

[thestranger66]

Hi Everyone,

I am trying to export charts from Excel to PowerPoint using VBA. I have run into an issue where long category labels fail to wrap when I export the chart. This yields a result where the axis labels take up as much space as the plot area of the chart does which is not ideal. I can fix this issue manually by changing the font of the category labels to 2, resizing the plot area to the desired width, and then changing the category label font back to the original font size. However, if I perform this same resizing operation through VBA, the text wrapping doesn't work.

My questions are:

Is there a property that I can modify directly to turn on text wrapping?
Is there some other creative solution anyone can think of to accomplish this task? It looks like there was a relevant page on jon peltier's site, but the link (http://peltiertech.com/Excel/Charts/FixFonts.html) is broken.

Thanks in advance!

My code:

With pptcht1
.Left = 40
.Top = 110
.Width = 640
.Height = 325
.Chart.Axes(xlCategory).TickLabels.Font.Size = 2
.Chart.PlotArea.Width = 600
.Chart.Axes(xlCategory).TickLabels.Font.Size = 8
End With​

 

[Legacy 421546]

Use

Const MAXCATEGORYLABELLENGTH = 20 ' Adjust as needed

If len(categoryLabel) > MAXCATEGORYLABELLENGTH then 
    categoryLabel = left(categoryLabel, MAXCATEGORYLABELLENGTH) &  vbCRLF  & ... etc ...
Endif

 

[thestranger66]

Hi johnny_j,

First, thanks for your reply. The solution you provided does work, but I am trying to avoid having to modify the table underlying the chart since those cells will routinely be copy/pasted elsewhere. Is there any way to leverage the built-in text wrapping function that I can initiate using the manual process above?

Thanks,
Steve

 

[thestranger66]

Sorry to do this, but I have to bump the thread.

 

[Jon Peltier]

Unfortunately there's no magic VBA command to manage wrapping of axis tick labels. The bottom line is, if you want the text to wrap reliably, you should insert a line feed in the cells with the axis tick labels. You do this by clicking on the cell, putting the cursor where you want the text to wrap, and holding Alt while you press the Enter key.

That old FixFonts page is so old that I don't even remember what it was for. If it were still relevant to modern version of Excel, it would still be available.

 

[thestranger66]

Thanks for the reply, Jon.

Can anyone help me return the screen coordinates of a powerpoint chart plot area? More specifically, I'm am trying to place the mouse cursor over the horizontal drag button for the plot area so that I can simulate the manual operation described above using VBA. I seem to be able to do everything except locate that horizontal drag button.

Thanks in advance!