Dynamically chart active row

[Patti]

I'm sure I saw a web page that showed how to create a dynamic chart that
allowed you to move your cursor to any row and have the chart update with the
data from that row. Does anyone know of a URL for something like this?

Thanks!

Patti

 

[Don Guillett]

Here is one I use to chart a column from a double click event in sheet code.
This is more complicated that you need. I must leave the office for the day
but can help tomorrow.
Modify to suit.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As
Boolean)
If Target.Column > 1 And Target.Row = 3 Then
[f1] = ActiveCell.Column - 1
'[f2] = Replace(UCase(Cells(3, ActiveCell.Column)), "^", "") _
'& " From " & [symbols!b2] & " To " & [symbols!c2]
[F2] = Cells(3, ActiveCell.Column) & " From " & [symbols!b2] & " To " &
[symbols!c2]
Sheets("Chart").Select
End If
End Sub

 

[Ed Ferrero]

Hi Patti,

Look for 'Chart Selector' here http://edferrero.m6.net/charting.html

Ed Ferrero

 

[Patti]

Thanks to both for replying.

The chart selector reflects what I was looking for, but I don't see any
instructions on how to create it. I see you are using a named range with
offset, etc, but I'm not sure exactly how to duplicate it.

Patti

 

[Ed Ferrero]

Hi Patti,

The 'Chart Selector' runs VBA code on the SheetSelectionChange event. The
code should have enough comments to make it understandable - basically, it
looks for cells that are formatted in bold with a certain colour and assumes
that these are chart headers.

There are two ways to use this in your application;

1) Easy way - paste your data in 'Chart Selector'
Change the row and column headings to what you require, and paste your
data into the worksheet.
Save As... whatever you like.

2) Copy the bits that make this work into a new workbook
Open the VB Editor, copy the code in the Workbook pane to your workbook.
Unhide both charts and copy across to your workbook.
(There are two charts embedded in the worksheet - chtDim,and chtMeasure)
Format a row and a column in Bold and with
a grey background (interior.colorIndex = 15) - for chtDim
or a blue background (interior.colorIndex = 37) - for chtMeasure
Build the named range xAxis, which is a dynamic range to cover
the header row, in my case it is
=OFFSET(Sheet1!$B$7,0,0,1,COUNTA(Sheet1!$7:$7)-1)
Change the number 7 to whatever your row is.

Ed Ferrero

 

[Jon Peltier]

Patti -

John Walkenbach (http://j-walk.com) covers it in the Interactive Charting section of
his Excel Charts book. I believe it's essentially the same technique as Ed describes.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______

 

[Patti]

Thanks for the help Ed!

Hi Patti,

The 'Chart Selector' runs VBA code on the SheetSelectionChange event. The
code should have enough comments to make it understandable - basically, it
looks for cells that are formatted in bold with a certain colour and assumes
that these are chart headers.

There are two ways to use this in your application;

1) Easy way - paste your data in 'Chart Selector'
Change the row and column headings to what you require, and paste your
data into the worksheet.
Save As... whatever you like.

2) Copy the bits that make this work into a new workbook
Open the VB Editor, copy the code in the Workbook pane to your workbook.
Unhide both charts and copy across to your workbook.
(There are two charts embedded in the worksheet - chtDim,and chtMeasure)
Format a row and a column in Bold and with
a grey background (interior.colorIndex = 15) - for chtDim
or a blue background (interior.colorIndex = 37) - for chtMeasure
Build the named range xAxis, which is a dynamic range to cover
the header row, in my case it is
=OFFSET(Sheet1!$B$7,0,0,1,COUNTA(Sheet1!$7:$7)-1)
Change the number 7 to whatever your row is.

Ed Ferrero

 

[Tushar Mehta]

A non-programmatic solution that redraws the graph with the recalculate
key (F9 on a Wintel machine):

Suppose the data are in rows 4 onwards, starting with column A. Also
suppose the number of columns with data is *not* known *and* could vary
from row to row.

Select row 4. Create a named formula (Insert | Name > Define...)

CurrRow =OFFSET(Sheet1!$A4,0,0,1,COUNTA(Sheet1!4:4))

Note the use of both absolute and relative addresses in the formula.

Now, create a chart using a named formula. If you don't know how see
Names in Charts
http://www.tushar-
mehta.com/excel/newsgroups/dynamic_charts/names_in_charts.html

Select any cell in any row with data. Press F9 and the chart will show
the data in that row.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions