Excel VBA - Read the series' chart data ranges
 

Consider a line graph with two Y axis:

I can create a graph, and I can change its data range


name_plan = "sheet1" ' sheet name
name_col = InputBox("Row the columns' given name", , 1)
col_y1 = InputBox("Primary column number", , 2)
col_y2 = InputBox("Secondary column number", , 3)
start_col = InputBox("Row the columns start", , 2)
end_col = InputBox("Row the columns end", , 25)

'Defining the data range

' Range to Y1 axis
start_cel1 = "R" & start_col & "C" & col_y1
end_cel1 = "R" & end_col & "C" & col_y1
name_cel1 = "R" & name_col & "C" & col_y1

' Range to Y2 axis
start_cel2 = "R" & start_col & "C" & col_y2
end_cel2 = "R" & end_col & "C" & col_y2
name_cel2 = "R" & name_col & "C" & col_y2

' Range to X1 and X2 axis
event_col = 1 ' 'This collum goes to X axis
start_axis1 = "R" & start_col & "C" & event_col
end_axis1 = "R" & end_col & "C" & event_col
start_axis2 = start_axis1
end_axis2 = end_axis1

'Defining the SeriesCollections
ActiveChart.SeriesCollection(1).Values = "=" & name_plan & "!"
start_cel1 & ":" & end_cel1
ActiveChart.SeriesCollection(2).Values = "=" & name_plan & "!"
start_cel2 & ":" & end_cel2
ActiveChart.SeriesCollection(1).Name = "=" & name_plan & "!"
name_cel1
ActiveChart.SeriesCollection(2).Name = "=" & name_plan & "!"
name_cel2
ActiveChart.SeriesCollection(1).XValues = "=" & name_plan & "!"
start_axis1 & ":" & end_axis1
ActiveChart.SeriesCollection(2).XValues = "=" & name_plan & "!"
start_axis2 & ":" & end_axis2



It fulfill my needs, but I don't know how to read th
ActiveChart.SeriesCollection().Ranges, in some how I will supply thi
information to procedure or even print it with Msgbox.

For exemple, image I have the "sheet2" with equal data disposition, bu
"sheet2" is related to another period, and I have a plent of graph
related to "sheet1", therefore I just change all the ranges wit
"sheet1" to "sheet2"

--
Message posted from http://www.ExcelForum.com