0

I tried to use this code to copy a select few charts from one sheet to another and then change the font size of all the charts selected. I can copy the charts across but the program breaks when I try to change the font size - can someone help please?

Sub arrangeplots()
'Arranges plots in plotspdf in a printable format

Dim OutSht As Worksheet
Dim Chart As ChartObject
Dim PlaceInRange As Range

Set OutSht = ActiveWorkbook.Sheets("plotspdf") '<~~ Output sheet
Set PlaceInRange = OutSht.Range("B2:J21")        '<~~ Output location

ActiveSheet.Shapes.Range(Array("Chart 11", "Chart 3", "Chart 4", "Chart 7", "Chart 8")).Select
Selection.Copy
OutSht.Paste PlaceInRange

For Each Chart In Sheets("plotspdf").ChartObjects
    With Chart.ChartArea.Format.TextFrame2.TextRange.Font
    .Size = 8
    End With
Next Chart

'Loop charts
'For Each Chart In Sheets("plots").ChartObjects
    'Copy/paste charts
    'Chart.Copy
    'OutSht.Paste PlaceInRange
    'Next Chart

End Sub
braX
  • 11,506
  • 5
  • 20
  • 33
apang
  • 93
  • 1
  • 12

2 Answers2

0

Try please this approach:

If Chart.Chart.ChartArea.Format.TextFrame2.HasText Then
        Chart.Chart.ChartArea.Format.TextFrame2.TextRange.Font.Size = 8
End If

Your Chart is the object helping in iteration. Its Chart property is what you need. In such a situation it is recommended to avoid using a Chart variable. It is better to use Ch for instance and in such a case, what I tried to emphasize above will be easier to digest, I think/hope...

The above piece of code must replace yours:

With Chart.ChartArea.Format.TextFrame2.TextRange.Font
    .Size = 8
End With

And please declare your Chart variable As ChartObject, instead of Chart.

Besides that, your code contains a range made based an array:

ActiveSheet.Shapes.Range(Array("Chart 11", "Chart 3", "Chart 4", "Chart 7", "Chart 8")).Select 'Selection.Copy

In order to use it, no need to select, no need to Copy... After that, you do not need/use at all this range in your code. You iterate between the charts of that active shape. Do you have something else in mind for later use of that chart from the clipboard?

Since I (now) understood that you intend to use it in a later stage, it can be used without selecting, like this:

ActiveSheet.Shapes.Range(Array("Chart 11", "Chart 3", "Chart 4", "Chart 7", "Chart 8")).Copy
FaneDuru
  • 38,298
  • 4
  • 19
  • 27
  • I tried using the code but I'm not sure if I'm using it correctly. Where do I place it in the code? – apang Feb 28 '20 at 11:24
  • Also about the array - that was the only solution I found to work to copy a selected few charts to another worksheet. I'm not sure if there is a much efficient way but I think there must be... – apang Feb 28 '20 at 11:25
  • I will edit my code and explain it there... Edited and explained how to use the code. I forgot about the array copying... – FaneDuru Feb 28 '20 at 11:27
  • Hi there sorry for the extremely late reply... I tried the code above and it didn't seem to work.. not sure whats wrong with it – apang Mar 02 '20 at 09:52
  • If you will answer this week, can you say, at least, if the code returns an error? If yes, what error and on which line? If not, what it does against what you would like it to do? Did you replace part of your code with the one posted by me? – FaneDuru Mar 02 '20 at 09:57
  • Sorry about that, had to go and couldn't get access to a computer. The code runs but theres not error produced. I managed to get it to run however using this code 'For Each Chart In Sheets("plotspdf").ChartObjects With Chart.Chart.ChartArea.Format.TextFrame2.TextRange.Font .Size = 8 End With Next Chart' – apang Mar 02 '20 at 10:05
  • @apang: And it did not change the font size as you wish? I would suggest to try 20 instead of 8. Just to be sure that it does its job... – FaneDuru Mar 02 '20 at 10:08
-1

This was the solution that worked for me:

'Change font size
For Each Chart In Sheets("plotspdf").ChartObjects
   With Chart.Chart.ChartArea.Format.TextFrame2.TextRange.Font
         .Size = 8
   End With
Next Chart
FaneDuru
  • 38,298
  • 4
  • 19
  • 27
apang
  • 93
  • 1
  • 12