Mark has attended:
Excel VBA Intro Intermediate course
Excel Advanced - For Power Users course
Excel PowerPivot course
Excel Dashboards for Business Intelligence course
Changing Multiple Axis Labels
Hi,
I'm trying to write a sub routine that will work through each chart on a worksheet and change the vertical axis labels to the format code £##,##0,,""m"", however it keeps debugging though on the 'For Each cht In ActiveSheet.ChartObjects' line and I'm not sure why?
Sub format_turnover()
Dim cht As Chart
For Each cht In ActiveSheet.ChartObjects
cht.Axes(xlValue).Select
Selection.TickLabels.NumberFormat = "£##,##0,,""m"""
Next cht
End Sub
RE: Changing Multiple Axis Labels
Hi Mark,
Thank you for the forum question.
Yes it is not always easy to work with charts in VBA.
Please have a look at the codes below. I have created two codes both should work with your charts.
Sub format_turnover()
Dim cht As ChartObject
With ActiveSheet
For Each cht In .ChartObjects
cht.Select
ActiveChart.Axes(xlValue).Select
Selection.TickLabels.NumberFormat = "£ #,##0.00 ""M"""
Next cht
End With
End Sub
Sub format_turnover()
For i = 1 To ActiveSheet.ChartObjects.Count
ActiveSheet.ChartObjects(i).Activate
ActiveChart.Axes(xlValue).Select
Selection.TickLabels.NumberFormat = "£ #,##0.00 ""M"""
Next i
End Sub
Please let me know if it is not working.
Kind regards
Jens Bonde
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
