Excel chart Y axis v's series

Hi,

Is there a way to force Excel to keep a consistent format for charts. I have a clustered bar chart that has three bars that I am after. With the data that I have, if there is three or more batches, then the chart comes out correctly with the three columns (see Correct image) if there is less than 3, excel swaps the Y axis for the series data (see incorrect image) which is now out of my jurisdiction as it is all generated from an excel VBA script, not done manually.

What do I need to add to get these bits correct.

Kind regards

Rob

Correct.PNGIncorrect.PNG

Absolutely spot on MrShorty sir, that solved my problem. For others that may find this, here is the subroutine that creates a chart from dynamic data as I have found that the order definitely does matter.

Sub History_Chart()
    Dim hData As Range
    Dim hChart As Shape
    
    Set hData = Range(BatchSearch.Offset(0, 1), BatchSearch.End(xlToRight)).Resize(5)
    With inspHist.ChartObjects.Add(Left:=100, Width:=375, Top:=75, Height:=225)
        With .Chart
            .ChartType = xlColumnClustered
            .SetSourceData Source:=hData
            .PlotBy = xlRows
            .Parent.Name = "Inspection_Chart"
        End With
    End With
    inspHist.Visible = xlSheetVisible
    inspHist.Select
End Sub

BatchSearch is a globally defined "Range" Variable declared at the beginning of the module.

Kind regards
Rob

Hi, yes this is indeed about Chart creation. This part of the macro creates a chart of requested part number batch dates that appears on a sheet with a back button on it. when back is pressed, the chart itself is deleted. So it is always only a temporary thing. It was my work around for not knowing how to produce a pop up window from a user form. It works very well, except for this issue, which as you may have guessed by now cannot be fixed manually, it has to be done at the point of creation.

Is this possible?

Kind regards
Rob

P.S. It is not the X and Y axis that is being switched, it is the Y axis and the Series that is being switched. And I am after some VBA code to correct this.