Chart (2002)

[Selva]

Dear Helpers,
I have a Workbook, say W.xls
It has 3 Worksheets A, B, C.
Each Worksheet has say, 10 rows, 10 columns of numbers.
I want to:
Create one Chart, very quickly with:
A!A1 to A!J1 as X-axis labels ALL the time.
3 data series, say, line graphs, of Row 7 i.e. A7 to J7 from EACH WorkSheet onto the ONE chart.
I want to be able to quickly modify Row Number to 9, say, A9 to J9 and presto! the Chart shows 3 new lines!
It can be a VBA solution or otherwise.
Now I have to edit the "Source Data" field in the Dialog Box 3 times for each ROW Number change.
Thanking you in anticipation.

Selva

[HansV]

The following procedure does what you want in a simple test; I haven't stress-tested it.

Sub ChangeRow(lngOld As Long, lngNew As Long)
Dim cht As ChartObject
Dim ser As Series
Dim strFormula As String
Set cht = Worksheets("A").ChartObjects(1)
For Each ser In cht.Chart.SeriesCollection
strFormula = ser.Formula
strFormula = Replace(strFormula, "$A$" & lngOld, "$A$" & lngNew)
strFormula = Replace(strFormula, "$J$" & lngOld, "$J$" & lngNew)
ser.Formula = strFormula
Next ser
Set ser = Nothing
Set cht = Nothing
End Sub

It assumes that the chart is embedded in worksheet A, adapt as necessary. Call the procedure as follows:

ChangeRow 7, 9

to change the source of the data series from row 7 to row 9. If you like, you can create a macro that prompts for the row numbers:

Sub PromptRow()
Dim lngOld As Long
Dim lngNew As Long
lngOld = Application.InputBox(Prompt:="Enter current row", Type:=1)
If lngOld <= 0 Then Exit Sub
lngNew = Application.InputBox(Prompt:="Enter new row", Type:=1)
If lngNew <= 0 Then Exit Sub
ChangeRow lngOld, lngNew
End Sub

[sdckapr]

Another option using formulas instead of macros:
Create a NEW Sheet (call it D) whihc contains the info to chart.
In B1: K1 enter in the numbers 1 - 10 (for the 10 cols)
In B2 enter:
<pre>=+A!A1</pre>

copy this to C2:K2
In B3 enter:
<pre>=INDEX(A!$A$1:$J$11,$A$2,B$1)</pre>

In B4 enter:
<pre>=INDEX(B!$A$1:$J$11,$A$2,B$1)</pre>

In B5 enter:
<pre>=INDEX(C!$A$1:$J$11,$A$2,B$1)</pre>

Copy B3:B5 to C3:K5
In A2 enter 7

Create your chart from the data in D!B2:K5. B2:K2 is "X-labels", B3:K3 series from A, B4:K4 series B, B5:K5 series C. This is row 1 from sheet A as X, row 7 from each of the sheets for the "Ys"

Change A2 to whatever row you want to get from the all the sheets.

You could link cell A2 to a pulldown or a spinner and change it without code. I usually would create a chart sheet with the chart and add a combobox and or spinner from FORMS and add it right to chart sheet.

Steve

[Selva]

Dear HansV,
Thank you. Works great! One refinement please. Is it possible to make VBA to:
Take current row as lngOld automatically and only ask for lngNew?
Thank you.

[Selva]

Thanks Steve. Also works great.
Selva

[HansV]

>> Is it possible to make VBA to: Take current row as lngOld automatically and only ask for lngNew?

Yes, see this variation on my original reply:

Sub PromptRow()
Dim lngOld As Long
Dim lngNew As Long
' Use current row as old row
lngOld = Selection.Row
' Ask for new row
lngNew = Application.InputBox(Prompt:="Enter new row", Type:=1)
If lngNew <= 0 Then Exit Sub
ChangeRow lngOld, lngNew
End Sub

[Selva]

Thanks again. Works really great!
Selva.