0

I am creating a chart, in a form, using VBA Excel 2010. I have configured excel to use Chartspace and the chart is created using dynamic data correctly, but the presentation is not what I am looking for, but can't figure how to change it. Please see the section of code:

Private Sub UserForm_Initialize()
Dim row_count As Integer
Dim n As Long
Dim chart_data As Worksheet

Set chart_data = Worksheets("Sheet3")
row_count = chart_data.UsedRange.Rows.Count

Dim varCats()
Dim varVals()

ReDim varCats(row_count)
ReDim varVals(row_count)

'Set c = ChartSpace1.Constants
Set mychart = ChartSpace1.Charts.Add
mychart.Type = xlColumnClustered '51 'chChartTypeBarClustered 'c.chChartTypeBarClustered

For n = 2 To row_count
    varCats(n) = ActiveWorkbook.Sheets("Sheet3").Range("A" & n).Value
    varVals(n) = ActiveWorkbook.Sheets("Sheet3").Range("T" & n).Value
Next n

mychart.SeriesCollection.Add
With mychart.SeriesCollection(0)
    .SetData chDimSeriesNames, chDataLiteral, "QAR Score"
    .SetData chDimCategories, chDataLiteral, varCats
    .SetData chDimValues, chDataLiteral, varVals
End With

End Sub

The bar chart is showing the bars horizontally and not vertically. So where I thought my variable "varCats" would go to the X-Axis is not but rather the Y-axis.

I know this is going to be a simple response from the community, but I have yet to find it through my searching.

Thanks, C

Image from the answer provide: enter image description here

Desired Results: enter image description here

Community
  • 1
  • 1
cquadrini
  • 769
  • 2
  • 12
  • 25

1 Answers1

0

Revised to work with ChartSpace objects in UserForm

Private Sub UserForm_Initialize()
    Dim row_count As Integer
    Dim n As Long
    Dim chart_data As Worksheet
    Dim srs As ChSeries
    Dim myChart As ChChart

    Set chart_data = Worksheets("Sheet3")
    row_count = chart_data.UsedRange.Rows.Count

    ReDim varCats(1 To row_count)
    ReDim varVals(1 To row_count)

    varCats = Application.Transpose(chart_data.Range("A2:A" & row_count).Value)
    varVals = Application.Transpose(chart_data.Range("T2:B" & row_count).Value)

    'Set c = ChartSpace1.Constants
    Set myChart = ChartSpace1.Charts.Add
        myChart.Type = chChartTypeColumnClustered
    Set srs = myChart.SeriesCollection.Add
        With srs
            .SetData chDimSeriesNames, chDataLiteral, "QAR Score"
            .SetData chDimCategories, chDataLiteral, varCats
            .SetData chDimValues, chDataLiteral, varVals
        End With



End Sub
David Zemens
  • 53,033
  • 11
  • 81
  • 130
  • This still produced columns oriented horizontally , and is NOW (my bad) 3D. I also did a macro, it gave me the same `Type` and still generated a 3-D horizontal bar chart. – cquadrini May 21 '14 at 16:33
  • 1
    can you please clarify: "columns" are always vertical, "bars" are always horizontal. You seem to be using the two terms interchangeably, and this is confusing. Consider adding a screenshot of the current output, and also of the desired output. – David Zemens May 21 '14 at 16:36
  • Also, please note that you do not mention anything about 3-d. So, please revise to describe what you actually want, in more detail. – David Zemens May 21 '14 at 16:36
  • 1
    So my desired results would be columns, yet the return is bars. I want 2 dimensional, yet the return, based on the answer, is now 3-D. I will upload images. – cquadrini May 21 '14 at 16:39
  • The 3d chart you display is not possibly produced by the code I provided. Can you include ALL of your code? It is possible you have some code elsewhere that is changing the chart type. – David Zemens May 21 '14 at 16:47
  • OK, are you trying to add this chart to a ChartSheet or a Worksheet? – David Zemens May 21 '14 at 16:57
  • 1
    The "project" has 2 forms, one that takes in data, and on button click enters it into an excel worksheet. The second form, is a form with a ChartSpace object on the form that takes the data from the worksheet and displays it. – cquadrini May 21 '14 at 17:01
  • This fixed it `mychart.Type = chChartTypeColumnClustered` – cquadrini May 21 '14 at 17:13
  • 1
    I revised my answer above, I think it will clean up your code a little bit, too. – David Zemens May 21 '14 at 17:23