Adding a data series to a chart

[Grant Reid]

Hi

I have a routine that adds data series to a chart. The chart resides in
Worksheet "Server 1" and is ChartObjects("Chart 3"). The routine selects the
chart and adds the data series.
Ideally I would like to be able to trigger this routine by clicking on a
button in another worksheet and add the data series without having to
navigate to the worksheet and select the chart. Can this be done? I have
enclosed my code below.

Sub AddSeries()
ActiveSheet.ChartObjects("Chart 3").Activate
ActiveChart.ChartArea.Select
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(1).XValues = Sheets("Server 2 Database
Space Summary").Range("B6:B19")
ActiveChart.SeriesCollection(1).Values = Sheets("Server 2 Database Space
Summary").Range("E6:E19")
ActiveChart.SeriesCollection(1).Name = "=""MB Used"""
ActiveChart.SeriesCollection(2).XValues = Sheets("Server 2 Database
Space Summary").Range("B6:B19")
ActiveChart.SeriesCollection(2).Values = Sheets("Server 2 Database Space
Summary").Range("F6:F19")
ActiveChart.SeriesCollection(2).Name = "=""MB Free"""
End Sub

Hope someone can help. Many thanks in advance - Grant

 

[Tom Ogilvy]

Dim cht as Chart
set cht = Worksheets("Server 1").ChartObjects("Chart3").Chart

now you can use the cht reference to refer to your chart.

 

[Grant Reid]

Hi

Firstly, thank you very much for your earlier response Tom. Much
appreciated.

However I need to add another layer of complexity to my routine. Once again
the routine is triggered by clicking on a button on an arbitary sheet, say
"Admin".

I have a number of sheets with the following naming convention "Server1
Graphics", "Server2 Graphics" etc etc.... On each of these sheets I have
have a chart object ChartObjects("Chart 3").

I also have a number of sheets with the following naming convention "Server
1 Database Space Summary", "Server 2 Database Space Summary" etc etc.....
This is where the source data for Chart Object 3 resides.

What I am attempting to do is loop through the sheets, locate all sheets
like "Server * Graphics" and the add the source data from the corresponding
"Server * Database Space Summary" sheet eg: "Server2 Graphics" chart object
3 derives its data from "Server 2 Database Space Summary". I'm using a
rather convoluted method for determining the source data sheet (there might
be a better way) see strSheetNum and strRef, but do not know how to pass
this derived sheet name to the routine.

I get a run-time error '9' Subscript out of range error. I have enclosed my
code below (thereby exposing myself as novice;-).

Sub AddSeries()
Dim wksSheet As Worksheet
Dim strChart As Chart
Dim strSheet As String
Dim strSheetNum As String
Dim strRef As String
strSheet = "Server* Graphics"
For Each wksSheet In ThisWorkbook.Worksheets
If wksSheet.Name Like strSheet Then
strSheetNum = Left(Mid(wksSheet.Name, 7, 20), Len(Mid(wksSheet.Name,
7, 20)) - 9)
strRef = "Server " & strSheetNum & " Database Space Summary"
wksSheet.Range("J1").Formula = strRef
Set strChart = Worksheets(wksSheet.Name).ChartObjects("Chart
3").Chart
strChart.SeriesCollection.NewSeries
strChart.SeriesCollection.NewSeries
strChart.SeriesCollection(1).XValues =
Sheets(strRef).Range("B6:B19")
strChart.SeriesCollection(1).Values = Sheets(strRef).Range("E6:E19")
strChart.SeriesCollection(1).Name = "=""MB Used"""
strChart.SeriesCollection(2).XValues =
Sheets(strRef).Range("B6:B19")
strChart.SeriesCollection(2).Values = Sheets(strRef).Range("F6:F19")
strChart.SeriesCollection(2).Name = "=""MB Free"""
End If
Next
End Sub

Hope someone can help

Many Thanks - Grant

 

[Grant Reid]

Hi

Managed to sort this one out. String manipulation error. Doh!

Regards - Grant