Excel-VBA Chart series name not showing on X Axis

Question

I've been making excel charts using VBA on a macro, I've done all the requirements I needed but one which is giving me the hardest time...

My problem is that when I make the charts I want to add on the X Axis the names of the series (bars) so it's easier to read, effect that I couldn't replicate with a macro, I'm able to set the series names and get them on the Legend but I can't get it to be on the axis

Ok, so I tried setting the series names like:

ActiveChart.SeriesCollection(k).Name = Range("O180").Value & " " & Range("F86").Value & vbNewLine & "Óptimo: 10-12"

I also tried setting up and XValues but it only accept Arrays, altho I have created a one entry Array but it didn't made what I wanted.

I also make some formating before and after I add all the series Tried:

ActiveChart.SetElement (msoElementPrimaryCategoryAxisTitleAdjacentToAxis)
ActiveChart.SetElement (msoElementPrimaryValueAxisTitleAdjacentToAxis)

after add

So this is how I add series, I add them all the same, after this begins another If Application.IsNA to add another serie....

If (Not Application.IsNA(Range("P163").Value)) Then
    If (Not Len(Range("P163")) <= 0) Then
        z = z + 1
        If (ActiveChart.SeriesCollection.Count < z) Then

            ActiveChart.SeriesCollection.NewSeries

        End If
        ActiveChart.SeriesCollection(z).Values = Range("W163").Value
        ActiveChart.SeriesCollection(z).Name = Range("O163").Value & " " & Range("F49").Value & vbNewLine & "Óptimo: 6,5-7,0 (Acidez Activa)"
        ActiveChart.SeriesCollection(z).Format.Fill.OneColorGradient msoGradientHorizontal, 1, 1
        ActiveChart.SeriesCollection(z).Format.Fill.GradientStops(1).Position = 0.25
        ActiveChart.SeriesCollection(z).Format.Fill.GradientStops(2).Position = 1
        'ActiveChart.SeriesCollection(z).Points(1).AxisGroup = z COMENTED LINE
        ActiveChart.SeriesCollection(z).Points(1).ApplyDataLabels
        ActiveChart.SeriesCollection(z) _
        .DataLabels.ShowSeriesName = True
        lastInput(1) = Range("O163").Value 'Name
        lastInput(2) = Range("F49").Value 'Unformated Value for Legend
        lastInput(3) = Range("W163").Value 'Formated value for chart
        lastInput(4) = "Óptimo: 6,5-7,0 (Acidez Activa)" 'Complementary Optimal Legend Entry
    End If
End If

I wanted the name on the XAxis bellow the bar, so each bar has its name

I hope this image is understandable, please pay no mind to all the problems the chart in the picture has, only to the one I'm having trouble with...

Read Jon Peltier's page on Axis Labels That Don't Block Plotted Data for some ideas. While his approach is not VBA based, it illustrates how it can be done and you can work your code in a similar way. — PeterT
Hello Peter, thanks for the reply, the problem is that i cant get the label on the negative side of the x axis, which in your response is the standart, the solution in your response is if you have negative values the label will stay put thus overlapsing the bar, my problem is that i cant get the standart to work, it should by deafult set the bar names on the negative side of axis P.S: The label youre seeing on top of the bar is the method '''ActiveChart.SeriesCollection(z).Points(1).ApplyDataLabels ActiveChart.SeriesCollection(z) _ .DataLabels.ShowSeriesName = True''' — Nuno Espinho
Setting the Values for a series is most often done using an array. In your code you're only using the value of a single cell to set all the values in the series (Range("W163").Value). Is that your intention? — PeterT
@PeterT Yes, i'm adding Series and filling the data with single cell contents (one by one, one after the other) as you can see in the .Values = and .Name = ..., so the one above is just the blue bar, the other bars are copied blocks of the blue bar, so I add a new Serie and populate it again, however when i show my xlCategory isntead of showing the names of the series I get a "1" for all the Series I created as if they are in some sort of group. Is this caused by the type of chart thats used to compare groups of series or what am I missing here? Thanks for you perseverance Peter :) — Nuno Espinho
Can you share a copy of some example data that the chart is built from? — PeterT

chillin chillin · Accepted Answer · 2019-04-19T13:20:43

Small detail. I think you can get labels beneath your x-axis (hopefully?) by doing the following:

Right-click your chart
Click Select Data
Click Change Row/Column
Click OK

But now you no longer have four series (each with one value), but rather a single series of four values. This may have implications for your formatting, as all bars might now be the same colour (since default colours are assigned per series, not per value in a series) -- and your charts's legend (if there is one) might only be showing one entry now.

When you create/use existing series in your code, you assign: Values, Name -- but you never assign x-values (which in the context of a bar chart show up as category names beneath your x-axis).

Excel is creating x-value labels for you (since you didn't assign any), which by default are a list of numbers (1, 2, 3, ...) matching the length of your series. Since all of your chart's series only have 1 value, you see a single label of 1 beneath your x-axis.

An approach that seems easier to me is to store your data like this in some worksheet (I just made up some data):

Then you could use some code like the below:

Option Explicit

Private Sub PlotChart()
    Dim someSheet As Worksheet
    Set someSheet = ThisWorkbook.Worksheets("Sheet1") ' Name of sheet containing data in my case.

    Dim someChart As Chart
    Set someChart = someSheet.ChartObjects(1).Chart

    Dim someSeries As Series
    Set someSeries = someChart.SeriesCollection.NewSeries

    With someSeries
        .Values = someSheet.Range("B3:B6")
        .XValues = someSheet.Range("A3:A6")
    End With

    someChart.ChartTitle.Text = "Balanco Acido-Base"
End Sub

Which should give you something like this:

You could (via VBA) change the formatting (fill, colours, etc.) of each Point in your Series -- if you need the bars to be different colours.

I think that if you want to continue with your current approach (of plotting a 1-value-long series for each bar), that it will not possible for you to include category names beneath your x-axis (even if you assign the correct Series.XValues for each series) -- and that you should instead plot one series consisting of all values.

That is my view, but maybe someone can tell you differently.

Excel-VBA Chart series name not showing on X Axis

1 Answers