Announcement

Collapse
No announcement yet.

Use of MS Chart Control in VBA Form

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Use of MS Chart Control in VBA Form



    I am trying to use the Chartspace object on a VBA form in Excel 2002, but am unable to find out how to specify the speadsheet data to be used for each series.

    I have found out how to add series, and to add titles & legend etc. but can anybody help with the actual data.

  • #2
    Re: Use of MS Chart Control in VBA Form

    Can you post and example of what you have so far?

    Cheers
    Andy

    Comment


    • #3
      Re: Use of MS Chart Control in VBA Form

      This is the code that I have so far - it adds a chart to the chartspace on the form along with title and axis labels. It also sets the colour of series(0) in the legend, but no values are displayed.

      Sub AddChart()
      ChartSpace1.Charts.Add
      ChartSpace1.Charts(0).Type = chChartTypeColumnClustered
      ChartSpace1.Charts(0).HasTitle = True
      ChartSpace1.Charts(0).HasLegend = True
      ChartSpace1.Charts(0).Title.Caption = "Test Chart"
      ChartSpace1.Charts(0).SeriesCollection.Add
      ChartSpace1.Charts(0).Legend.Position = chLegendPositionTop
      ChartSpace1.Charts(0).SeriesCollection(0).Type = chChartTypeColumnClustered
      ChartSpace1.Charts(0).SeriesCollection(0).Interior.Color = vbRed
      ChartSpace1.Charts(0).SeriesCollection(0).Type = chChartTypeColumnClustered
      ChartSpace1.Charts(0).Axes(1).HasTitle = True
      ChartSpace1.Charts(0).Axes(1).Title.Caption = "Time"
      ChartSpace1.Charts(0).Axes(1).MajorTickMarks = True
      ChartSpace1.Charts(0).Axes(0).HasTitle = True
      ChartSpace1.Charts(0).Axes(0).Title.Caption = "Rate"
      End Sub

      Comment


      • #4
        Re: Use of MS Chart Control in VBA Form

        What is ChartSpace1 ?

        Cheers
        Andy

        Comment


        • #5
          Re: Use of MS Chart Control in VBA Form

          Chartspace1 is the name of the MS Office 9 Chart object that I added to the form. I have now found how to add values from an array, which I can populate from the speadsheet. I'm not sure if it is possible to reference the data directly to the spreadsheet, but for now I have a solution.

          Thanks

          Comment


          • #6


            Re: Use of MS Chart Control in VBA Form

            The help is fairly helpful
            This example creates a chart that is bound to a spreadsheet. The series name is in cell B1, the category names are in cells A2:A28, and the values are in cells B2:B28.

            Set c = ChartSpace1.Constants
            Set ChartSpace1.DataSource = Spreadsheet1
            ChartSpace1.Charts.Add
            ChartSpace1.Charts(0).Type = c.chChartTypeLineMarkers
            ChartSpace1.Charts(0).SetData c.chDimSeriesNames, 0, "b1"
            ChartSpace1.Charts(0).SetData c.chDimCategories, 0, "a2:a28"
            ChartSpace1.Charts(0).SeriesCollection(0).SetData c.chDimValues, 0, "b2:b28"

            Cheers
            Andy

            Comment

            Working...
            X