Results 1 to 7 of 7

Thread: Chart (2002)

  1. #1
    Lounger
    Join Date
    Dec 2001
    Posts
    48
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Chart (2002)

    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

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Chart (2002)

    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

  3. #3
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Chart (2002)

    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

  4. #4
    Lounger
    Join Date
    Dec 2001
    Posts
    48
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Chart (2002)

    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.

  5. #5
    Lounger
    Join Date
    Dec 2001
    Posts
    48
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Chart (2002)

    Thanks Steve. Also works great.
    Selva

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Chart (2002)

    >> 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

  7. #7
    Lounger
    Join Date
    Dec 2001
    Posts
    48
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Chart (2002)

    Thanks again. Works really great!
    Selva.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •