Hello,

in my latest project, I have to create bar-charts using the
ChartWizard-Method in Excel VBA.

The problem is:
I add the data ranges for the chart in a certain order to the "main"
data range. I add new ranges using the union(...) function
successively. The I use chartWizard() in order to set the source data.

For Example: The names for the bars are KY,ES,JX,OC,HV,NB,FT,DR, so I
add the corresponding data ranges in that order.

BUT...the bars are not displayed in the same order that I used when
adding the data ranges. Instead, the bars are (from top to bottom) in
this order: OC,NB,KY,JX,HV,FT,ES,DR.

Obviously, they are in reverse alphabetical order, but I want them to
be ordered in my own order!

So, how do I set the source data using my own order?

My Source code for my CreateChart() sub is below.

Thanks

Malte


Sub createChart(itemLong As String, itemShort As String,
itemIn2QuestIdx As Integer, idxRotation As Integer, newWorkBook As
Workbook)
Dim rotationRange As Range
Dim conceptRange As Range
Dim foundConcept As Boolean
Dim rotationcell As Range

Dim chartRange As Range
Dim newChart As Chart
Dim neuesDiagramm As Chart

Dim currentConceptName As String
Dim foundConceptIndex As Integer
Dim i As Integer
Dim errors As Boolean

Set rotationRange = getRotationRange(idxRotation)
Set conceptRange = getConceptRange()
errors = False


'alle Konzepte in Rotation durchlaufen
For Each rotationcell In rotationRange

(...determine chartRange...)

If chartRange Is Nothing Then
Set chartRange =
getDataRangeForConceptItem(foundConceptIndex, itemIn2QuestIdx,
newWorkBook, errors)
Else
Set chartRange = Union(chartRange,
getDataRangeForConceptItem(foundConceptIndex, itemIn2QuestIdx,
newWorkBook, errors))
End If
Else
End If
Else
MsgBox "Für die gewählte Rotation (" &
frmRotations.lbxRotations.Value & ") kann keine Entsprechung in der
Liste aller Konzepte gefunden werden.", , "Fehler!"
errors = True
End If
End If
Next 'nächstes Konzept der Rotation

If Not errors Then
'an dieser Stelle besitzt chartRange die notwendigen
Diagrammbereiche für Item Nr. itemIn2QuestIdx
If newWorkBook.Charts.count = 0 Then
Set newChart = newWorkBook.Charts.Add()
Else
Set newChart =
newWorkBook.Charts.Add(after:=newWorkBook.Charts(n ewWorkBook.Charts.count))
End If



'Chart-Eigenschaften festlegen...
newChart.Name = itemShort

'Set newChart = newWorkBook.Charts(newWorkBook.Charts.count)

newChart.ChartWizard chartRange, xlBar, , xlRows, 1, 0, True,
itemLong

newChart.Name = itemShort
newChart.SeriesCollection(1).Name = getLegendDescrAverage()
newChart.SeriesCollection(2).Name = getLegendDescrStdev()

newChart.Legend.Font.Name = "Arial"
newChart.Legend.Font.Size = 12

newChart.ChartTitle.Font.Size = 18

'X-Achse
newChart.Axes(xlValue).MaximumScale = getXAxisMaxValue()
newChart.Axes(xlValue).MinimumScale = getXAxisMinValue()
newChart.Axes(xlValue).MinorUnitIsAuto = False
newChart.Axes(xlValue).MajorUnitIsAuto = False
newChart.Axes(xlValue).HasMajorGridlines = True
newChart.Axes(xlValue).HasMinorGridlines = False
newChart.Axes(xlValue).CrossesAt = 0

'Y-Achse
newChart.Axes(xlCategory).HasMajorGridlines = False
newChart.Axes(xlCategory).HasMinorGridlines = False
'newChart.Axes(xlCategory).ReversePlotOrder = True
newChart.Axes(xlCategory).Crosses = xlMinimum
newChart.Axes(xlCategory).TickLabelSpacing = 1
newChart.Axes(xlCategory).TickMarkSpacing = 1
newChart.Axes(xlCategory).AxisBetweenCategories = True

newChart.SizeWithWindow = True

newChart.PlotArea.Fill.ForeColor.SchemeColor = 2
newChart.PlotArea.Fill.BackColor.SchemeColor = 15
newChart.PlotArea.Fill.TwoColorGradient msoGradientHorizontal,
1

End If
End Sub