CODEWITHSUNDEEP
vbaexcel
Jenny
Jenny

Reputation: 451

Changing the axis of chart to percentage

I have an sheet where I am generating the chart. I am generating Clustered Column Chart.I would like to change the y-axis of the chart to percentage. I tried the below code and it gives me an error

object doesnot support this property or method.

Could anyone tell, how I can change the axis.

Any lead would be helpful 

Sub chartResult()
Dim rng As Range
Dim cht As Object
Set rng = ActiveSheet.Range("B2:H53")
'ThisWorkbook.Sheets("Result").ChartObjects.Delete
Set sh = ActiveSheet.Shapes.AddChart
sh.Select
Set cht = ActiveChart
With cht
.SetSourceData Source:=rng
.ChartType = xlColumnClustered
cht.SeriesCollection(1).DataLabels = True
cht.SeriesCollection(1).DataLabels.NumberFormat = "0.0%"
End With
cht.SeriesCollection(1).Format.Fill.ForeColor.RGB = RGB(72, 118, 255) '<~~ Red
cht.SeriesCollection(2).Format.Fill.ForeColor.RGB = RGB(255, 0, 0)
cht.SeriesCollection(3).Format.Fill.ForeColor.RGB = RGB(0, 255, 0)
cht.HasTitle = True
cht.ChartTitle.Text = "Result 2017"
End Sub

Upvotes: 0

Views: 2912

Answers (2)

Jarom
Jarom

Reputation: 1077

Try this cht.Axes(xlSecondary).TickLabels.NumberFormat = "0.0%" instead of cht.SeriesCollection(1).DataLabels.NumberFormat = "0.0%"

Also, you can drop the cht inside of the with statement if you want. It is redundant but shouldn't cause a problem with the code.

Upvotes: 3

TomServo
TomServo

Reputation: 7409

I got the error on a different line so amended it as follows:

.ChartType = xlColumnClustered
cht.SeriesCollection(1).ApplyDataLabels Type:=xlDataLabelsShowLabel, LegendKey:=False, AutoText:=True, HasLeaderLines:=False, ShowSeriesName:=False, ShowCategoryName:=False, ShowValue:=True, ShowPercentage:=True
cht.SeriesCollection(1).DataLabels.NumberFormat = "0.0%"

And it works now ;)

Upvotes: 1

Related Questions