VBA Chart Series title with multiple cells

[mikejvir]

Hello All,

I am trying to make a more generic way to create a XY scatter chart and add data to the chart. Here is some test code.

Sub Test_Add_Series()

Dim DataTab As String
Dim PLTab As String
Dim Xrange As Range
Dim Yrange As Range
Dim Trange As Range
Dim Snu As Long

DataTab = "Optics_Measurement_OPM_Append_0"
PLTab = "PL_Power"

Set Xrange = Worksheets(DataTab).Range(Cells(5, 8), Cells(7005, 8))
Set Yrange = Worksheets(DataTab).Range(Cells(5, 9), Cells(7005, 9))
Set Trange = Worksheets(DataTab).Range(Cells(3, 9), Cells(4, 9))
Snu = 1

Call CreateChartPage(PLTab)
Call Add_Series(DataTab, PLTab, Xrange, Yrange, Trange, Snu)

End Sub

Sub CreateChartPage(ChartDataTab As String)

  Range("EE1:EF5").Select
  Charts.Add.Name = ChartDataTab
  Charts(ChartDataTab).ChartType = xlXYScatterLinesNoMarkers
  Charts(ChartDataTab).Location Where:=xlLocationAsNewSheet
  Charts(ChartDataTab).SeriesCollection(1).Delete

End Sub

Sub Add_Series(DataTab As String, PLTab As String, Xrange As Range, Yrange As Range, Trange As Range, SPO As Long)
'

'  Generic Subroutine for creating a XY Scatter Graph in a seperate Chart Tab

' DataTab   : String   - Tab data is stored in
' PLtab     : String   - Chart will be put in this Tab
' Xrange    : Range    - Range for X axis Data
' Yrange    : Range    - Range for Y axis Data
' Trange    : Range    - Range for Series title
' SPO       : Long     - Series Plot order
'
    Application.ScreenUpdating = False
  '  Sheets(PLTab).Select
    Charts(PLTab).Activate
        
    ActiveChart.SeriesCollection.NewSeries
    ActiveChart.SeriesCollection(SPO).XValues = Xrange
    ActiveChart.SeriesCollection(SPO).Values = Yrange
    ActiveChart.SeriesCollection(SPO).Name = Worksheets(DataTab).Range("I3:I4") ' Trange
End Sub

I am stuck on the label for the series. The name is in multiple rows and when manually created this is fine. I tried both passing a range in and defined the range. Everything else is working as expected. I am missing something but can not see it.

Thanks in Advance.

 

[NdNoviceHlp]

Hi mikejvr. It seems like this will work. The cells have to be added individually. The CStr is just to ensure that the cell values are used as a string as the name will error unless it receives a string input. Also, your code doesn't reset the screenupdating before you exit the sub. HTH. Dave

ActiveChart.SeriesCollection(SPO).Name = CStr(Worksheets(DataTab).Range("I3")) & CStr(Worksheets(DataTab).Range("I4"))
Application.ScreenUpdating = True

 

[mikejvir]

Hi mikejvr. It seems like this will work. The cells have to be added individually. The CStr is just to ensure that the cell values are used as a string as the name will error unless it receives a string input. Also, your code doesn't reset the screenupdating before you exit the sub. HTH. Dave

ActiveChart.SeriesCollection(SPO).Name = CStr(Worksheets(DataTab).Range("I3")) & CStr(Worksheets(DataTab).Range("I4"))
Application.ScreenUpdating = True

Dave,

Thanks. That is a work around, but it forces the series label to be a string.

=SERIES("OPMPower",Optics_Measurement_OPM_Append_0!$H$5:$H$7005,Optics_Measurement_OPM_Append_0!$I$5:$I$7005,1)

but this is what I really am looking for

=SERIES(Optics_Measurement_OPM_Append_0!$I$3:$I$4,Optics_Measurement_OPM_Append_0!$H$5:$H$7005,Optics_Measurement_OPM_Append_0!$I$5:$I$7005,2)

I do this because I use Pivot Tables for plotting and the series label will change depending on the Pivot table values.

Mike Virostko

 

[RoryA]

Try using:

    ActiveChart.SeriesCollection(SPO).Name = "='" & DataTab & "'!" & Trange.Address

 

[mikejvir]

Try using:

    ActiveChart.SeriesCollection(SPO).Name = "='" & DataTab & "'!" & Trange.Address

Thanks Rory. This works.

Mike Virostko