4

I have a bunch of charts in which the chart-title just doesn't fit on one line in the title-box.

enter image description here

Rather than reducing the font size further, I'd prefer to make the textbox a little wider.

Printing the current width of the charttitle to the immediate window, I see that it is currently ~245 px wide. Seeing this, I decided to use the following code to attempt to resize it for all charts of the given name in the worksheets:

Dim ws As Worksheet
Dim co As ChartObject

For Each ws In ThisWorkbook.Worksheets
    For Each co In ws.ChartObjects
        If co.Name = "Tiltaksplan" Then
            co.Chart.ChartTitle.Width = 260
        End If
    Next co
Next ws

However it appears that .ChartTitle.Width is a read only property, so when I try to change it the macro stops with an error on that line.

Is there any other way to resize the title box, or am I stuck doing it by hand?

Pᴇʜ
  • 56,719
  • 10
  • 49
  • 73
eirikdaude
  • 3,106
  • 6
  • 25
  • 50
  • 2
    Don't think it's even possible to resize manually. The only workaround of which I'm aware is to remove the title and use a textbox instead. – SJR Feb 21 '19 at 14:40
  • If you want to resize the title of the chart, you cannot drag it to the size you want. You will have to change the font to meet the size you want. – Siddharth Rout Feb 21 '19 at 14:41

1 Answers1

3

Expanding on SJR's comment above, would the following work for you? 

Dim ws As Worksheet
Dim co As ChartObject

For Each ws In ThisWorkbook.Worksheets
    For Each co In ws.ChartObjects
        If co.Name = "Tiltaksplan" Then
            co.Chart.ChartTitle.Text = " " 
            co.Shapes.AddLabel(msoTextOrientationHorizontal, 180, _
                12, 72, 260).Select
            Selection.ShapeRange.IncrementLeft -130
            Selection.ShapeRange(1).TextFrame2.TextRange.Characters.Text = _
                "This is your new title"
        End If
    Next co
Next ws

That was slapped together from a recorded macro, so you might need to adjust the text box location singles.

SmrtGrunt
  • 869
  • 12
  • 25
  • I need to be able to treat the charts as single objects, so I don't think this approach is feasible. Thanks for the input though :-) – eirikdaude Feb 25 '19 at 11:06
  • I don't understand your comment: "treat the charts as single objects". This is exactly what I was going to suggest. – Jon Peltier Feb 28 '19 at 02:23
  • @JonPeltier Agreed. Both my code and the original code that I modified treat the charts as individual objects, so I'm not sure what the disconnect is. – SmrtGrunt Feb 28 '19 at 13:30