Change default axis category labels

[Chris H.]

Hi all,

I'm relatively new to VBA so perhaps it's a simple question for most people...

At the moment both axis of my chart have 5 values [0, 1, 2, 3, 4] per default. I tried to change those default values for the x-axis to other text strings using:

chtRisk.Chart.Axes(1).CategoryNames = Worksheets("WERTE").Range("I5:I9")

OR directly

chtRisk.Chart.Axes(1).CategoryNames = Array("neu", "stabilisiert", "etabliert", "überholt", "veraltet")

Unfortunately both ways didn't have any effect . There were still the values 0 to 4 at the x-axis. Do I using the wrong method? Has anybody an idea? Here the complete code:

' draw a diagram with (x,y) points
Set chtRisk = ActiveSheet.ChartObjects.Add _
(left:=hOffs1, width:=400, Top:=uTop, height:=300)
chtRisk.Chart.ChartType = xlXYScatter

chtRisk.Name = "Risikobewertung"
chtRisk.Chart.HasTitle = True
chtRisk.Chart.HasLegend = False
chtRisk.Chart.ChartTitle.Characters.Text = "Risikobewertung"

' format chart grid
chtRisk.Chart.Axes(1).MinimumScale = 0
chtRisk.Chart.Axes(1).MaximumScale = 4
chtRisk.Chart.Axes(1).MajorUnit = 1
chtRisk.Chart.Axes(1).HasMajorGridlines = True
chtRisk.Chart.Axes(1).HasTitle = True
chtRisk.Chart.Axes(1).CategoryNames = Array("neu", "stabilisiert", "etabliert", "überholt", "veraltet")
chtRisk.Chart.Axes(2).MinimumScale = 0
chtRisk.Chart.Axes(2).MaximumScale = 4
chtRisk.Chart.Axes(2).MajorUnit = 1
chtRisk.Chart.Axes(2).HasMajorGridlines = True

'format plot area
chtRisk.Chart.PlotArea.Select
With Selection
.Fill.Visible = True
.Fill.TwoColorGradient Style:=msoGradientDiagonalDown, Variant:=1
.Fill.ForeColor.SchemeColor = 3
.Fill.BackColor.SchemeColor = 43
End With

' chart values
chtRisk.Chart.SeriesCollection.NewSeries
chtRisk.Chart.SeriesCollection(1).XValues = Range(Cells(11, 12), Cells(11, iCol - 1))
chtRisk.Chart.SeriesCollection(1).Values = Range(Cells(100, 12), Cells(100, iCol - 1))
For Counter = 1 To iCol - 12
chtRisk.Chart.SeriesCollection(1).Points(Counter).HasDataLabel = True
chtRisk.Chart.SeriesCollection(1).Points(Counter).DataLabel.Text = Cells(3, Counter + 11)
Next Counter

' Bug? Set again the chart type, otherwise the points are connected with lines...
chtRisk.Chart.ChartType = xlXYScatter


Thanks for your help in advance.
Chris

 

[Chris H.]

If someone is interested in the solution:
1) It doesn't work with xlXYScatter chart type --> take xlLine
2) Put the chart values (SeriesCollection) directly after chart type declaration
3) Set Border.LineStyle = xlNone to remove lines between the points (since I want a point diagram)
4) Now when using CategoryNames it works as expected...

Bye
Chris