Changing chart tick label properties (color, font size, number format) on xlHistogram Chart

RawlinsCross

Active Member
Joined
Sep 9, 2016
Messages
332
Having a bit of trouble doing basic chart property adjustment. I'm wondering whether it has to do with the chart type itself? From all my searching, the syntax seems correct?

VBA Code:
Set myChart = ActiveSheet.Shapes.AddChart2(366, xlHistogram).Chart

With myChart
    .SeriesCollection.NewSeries
    'Histograph can't take array input??
    .FullSeriesCollection(1).Values = wSht.Range("J1").CurrentRegion
    .FullSeriesCollection(1).ApplyDataLabels  
    .HasTitle = False
    .Axes(xlCategory).MajorTickMark = xlOutside
    .Axes(xlCategory).TickLabels.NumberFormat = "0.0" 'This fails
    .Axes(xlCategory).TickLabels.Font.Size = 10 ' This fails
    .Axes(xlCategory).TickLabels.Font.Color = vbBlack 'This fails
    .Axes(xlValue).MajorTickMark = xlOutside
    .Axes(xlValue).TickLabels.Size = 10 'This fails
    .Axes(xlValue).TickLabels.Font.Color = vbBlack 'This fails
End With
 

Some videos you may like

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.

rlv01

Well-known Member
Joined
May 16, 2017
Messages
771
Use the macro recorder to record a macro while you are manually making the changes you want. Then inspect the recorded code to see what the syntax should be.
 

RawlinsCross

Active Member
Joined
Sep 9, 2016
Messages
332
No dice.. macro doesn't record changes. I read somewhere that this is an issue with new graphing tools rolled out in 2016 but I figured I'd ask to see if there had been any update.
Thanks for your suggestion all the same. (First thing I tried)
 

rlv01

Well-known Member
Joined
May 16, 2017
Messages
771
If it ever was a problem in 2016, they have fixed it. I have Excel 2016/O365 and the macro recorder works for me when I edit chart axes. Btw,
VBA Code:
    .Axes(xlCategory).TickLabels.NumberFormat = "0.0" 'This fails
    .Axes(xlCategory).TickLabels.Font.Size = 10 ' This fails
Works just fine for me.
 

rlv01

Well-known Member
Joined
May 16, 2017
Messages
771
Sorry. My mistake, It just now registered that you are using a histogram chart object, which does not play by the normal rules.
 

Watch MrExcel Video

Forum statistics

Threads
1,126,963
Messages
5,621,855
Members
415,862
Latest member
nascaline

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top