1

I've written some code to create a second y-axis in a chart. I recorded a macro when I formated the second y-axis and the code looks great. However, it doesn't work when I try to run it from the VBA code.. I've tried the Format Painter (the first y-axis), which would be optimal if it worked, and also by formating the second y-axis "by hand"

This is what I have (and it's doesn't work..) Formating the second y-axis to Calibri ("by hand"): ActiveChart.Axes(xlValue, xlSecondary).Select Selection.Format.TextFrame2.TextRange.Font.Name = "+mj-lt" 'This line doesn't work... Selection.Format.TextFrame2.TextRange.Font.Size = 14

Error code: Method TextFrame2 of Object 'ChartFormat' failed

Using Format Painter:

ActiveChart.Axes(xlValue).Select
Selection.Format.PickUp    'This line doesn't work...
ActiveChart.Axes(xlValue, xlSecondary).Select

Error code: Object doesn't support this property or method.

Does anyone know why none of the methods work or if there's a work around?

Community
  • 1
  • 1
dcez
  • 21
  • 3
  • You should never (or at least always try to avoid) to use `select`. Take a look here http://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba-macros – Niclas Nov 03 '16 at 17:02
  • You can use `.PasteSpecial Paste:=xlPasteFormats` to do the "Format Painter". But you need to do a `.copy` of something before. – Niclas Nov 03 '16 at 17:05
  • Thanks for the advice Niclas. However, I don't think the copy paste method is possible when it comes to the y-axis in the chart.. – dcez Nov 04 '16 at 07:03

1 Answers1

1

I found a work around by using: 

With ActiveChart.Axes(xlValue, xlSecondary).TickLabels.Font
   .Name = "Calibri"
   .Size = 16
End with

I haven't been able to solve the issue with Format Painter though...

dcez
  • 21
  • 3
  • Your solution solved my axis font coloring problem. Based on your post, I ended up with With ActiveChart.Axes(xlValue, xlSecondary).TickLabels.Font .Color = RGB(255, 0, 0) End With – Michael Mar 25 '20 at 13:05