Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Chris Manning
 
Posts: n/a
Default Can I grab graph coordinates in Excel via the mouse with VB?

Is it possible to get the graph coordinates (i.e. the y coordinate of a
graph) in Excel using Visual Basic coding (or other methods) and insert them
into another cell? Specifically ,what I want to do is have the user click on
a graph, and have the y-coordinate at the time of the click entered into a
cell, or, alternatively, have the user click and drag across the graph and
have the coordinates at the beginning click and at the end of the drag
entered into two seperate cells. Further, the complete set of y-coordinates
is a column of data points, not a fcn if that helps at all.
  #2   Report Post  
Jon Peltier
 
Posts: n/a
Default

Chris -

You can trap events in the chart. I wrote an article about this:

http://www.computorcompanion.com/LPMArticle.asp?ID=221

and I have a couple examples showing how to get values by clicking on a
plotted point:

http://peltiertech.com/Excel/Charts/...html#PointInfo

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


Chris Manning wrote:

Is it possible to get the graph coordinates (i.e. the y coordinate of a
graph) in Excel using Visual Basic coding (or other methods) and insert them
into another cell? Specifically ,what I want to do is have the user click on
a graph, and have the y-coordinate at the time of the click entered into a
cell, or, alternatively, have the user click and drag across the graph and
have the coordinates at the beginning click and at the end of the drag
entered into two seperate cells. Further, the complete set of y-coordinates
is a column of data points, not a fcn if that helps at all.

  #3   Report Post  
Chip
 
Posts: n/a
Default

Great articles. My question relates to gridlines. Can I click a gridline and
get all the datapoints that intersect that gridline. My chart will have time
as the x-axis with many elements graphed as lines. I would like to display
the values of all elements at a given time represented by the gridline.

"Jon Peltier" wrote:

Chris -

You can trap events in the chart. I wrote an article about this:

http://www.computorcompanion.com/LPMArticle.asp?ID=221

and I have a couple examples showing how to get values by clicking on a
plotted point:

http://peltiertech.com/Excel/Charts/...html#PointInfo

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


Chris Manning wrote:

Is it possible to get the graph coordinates (i.e. the y coordinate of a
graph) in Excel using Visual Basic coding (or other methods) and insert them
into another cell? Specifically ,what I want to do is have the user click on
a graph, and have the y-coordinate at the time of the click entered into a
cell, or, alternatively, have the user click and drag across the graph and
have the coordinates at the beginning click and at the end of the drag
entered into two seperate cells. Further, the complete set of y-coordinates
is a column of data points, not a fcn if that helps at all.


  #4   Report Post  
Jon Peltier
 
Posts: n/a
Default

It's not a native Excel feature, but if you're clever, you can probably
work it out. You need to determine which gridline was clicked, then go
through each series to find line segments that cross this value, and
interpolate the Y value at the time in question (unless you happen to
have actual data points for each series at this time value).

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


Chip wrote:

Great articles. My question relates to gridlines. Can I click a gridline and
get all the datapoints that intersect that gridline. My chart will have time
as the x-axis with many elements graphed as lines. I would like to display
the values of all elements at a given time represented by the gridline.

"Jon Peltier" wrote:


Chris -

You can trap events in the chart. I wrote an article about this:

http://www.computorcompanion.com/LPMArticle.asp?ID=221

and I have a couple examples showing how to get values by clicking on a
plotted point:

http://peltiertech.com/Excel/Charts/...html#PointInfo

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


Chris Manning wrote:


Is it possible to get the graph coordinates (i.e. the y coordinate of a
graph) in Excel using Visual Basic coding (or other methods) and insert them
into another cell? Specifically ,what I want to do is have the user click on
a graph, and have the y-coordinate at the time of the click entered into a
cell, or, alternatively, have the user click and drag across the graph and
have the coordinates at the beginning click and at the end of the drag
entered into two seperate cells. Further, the complete set of y-coordinates
is a column of data points, not a fcn if that helps at all.


  #5   Report Post  
Chip
 
Posts: n/a
Default

I believe arg2 is what determines which gridline. Correct?

"Jon Peltier" wrote:

It's not a native Excel feature, but if you're clever, you can probably
work it out. You need to determine which gridline was clicked, then go
through each series to find line segments that cross this value, and
interpolate the Y value at the time in question (unless you happen to
have actual data points for each series at this time value).

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


Chip wrote:

Great articles. My question relates to gridlines. Can I click a gridline and
get all the datapoints that intersect that gridline. My chart will have time
as the x-axis with many elements graphed as lines. I would like to display
the values of all elements at a given time represented by the gridline.

"Jon Peltier" wrote:


Chris -

You can trap events in the chart. I wrote an article about this:

http://www.computorcompanion.com/LPMArticle.asp?ID=221

and I have a couple examples showing how to get values by clicking on a
plotted point:

http://peltiertech.com/Excel/Charts/...html#PointInfo

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


Chris Manning wrote:


Is it possible to get the graph coordinates (i.e. the y coordinate of a
graph) in Excel using Visual Basic coding (or other methods) and insert them
into another cell? Specifically ,what I want to do is have the user click on
a graph, and have the y-coordinate at the time of the click entered into a
cell, or, alternatively, have the user click and drag across the graph and
have the coordinates at the beginning click and at the end of the drag
entered into two seperate cells. Further, the complete set of y-coordinates
is a column of data points, not a fcn if that helps at all.




  #6   Report Post  
Jon Peltier
 
Posts: n/a
Default

Element ID is 15 (xlMajorGridlines) or 16 (xlMinorGridlines) when the gridlines are
selected.
Arg1 is always 1, since gridlines are only associated with primary axes.
Arg2 is 1 for the category (X) axis or 2 for the value (Y) axis.

Since you can only select the set of gridlines, not a single one, you cannot tell
which individual gridline was selected.

You can use a MouseUp event to get the X and Y coordinates of the click. These are
in chart window coordinates, not the X and Y of the chart axis coordinates. You can
determine which is the closest date based on this, but it takes a bunch of algebraic
calculations.

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

Chip wrote:
I believe arg2 is what determines which gridline. Correct?

"Jon Peltier" wrote:


It's not a native Excel feature, but if you're clever, you can probably
work it out. You need to determine which gridline was clicked, then go
through each series to find line segments that cross this value, and
interpolate the Y value at the time in question (unless you happen to
have actual data points for each series at this time value).

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


Chip wrote:


Great articles. My question relates to gridlines. Can I click a gridline and
get all the datapoints that intersect that gridline. My chart will have time
as the x-axis with many elements graphed as lines. I would like to display
the values of all elements at a given time represented by the gridline.

"Jon Peltier" wrote:



Chris -

You can trap events in the chart. I wrote an article about this:

http://www.computorcompanion.com/LPMArticle.asp?ID=221

and I have a couple examples showing how to get values by clicking on a
plotted point:

http://peltiertech.com/Excel/Charts/...html#PointInfo

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


Chris Manning wrote:



Is it possible to get the graph coordinates (i.e. the y coordinate of a
graph) in Excel using Visual Basic coding (or other methods) and insert them
into another cell? Specifically ,what I want to do is have the user click on
a graph, and have the y-coordinate at the time of the click entered into a
cell, or, alternatively, have the user click and drag across the graph and
have the coordinates at the beginning click and at the end of the drag
entered into two seperate cells. Further, the complete set of y-coordinates
is a column of data points, not a fcn if that helps at all.


  #7   Report Post  
Chip
 
Posts: n/a
Default

I had feared this originally. I am in a project that uses some built in
killer graphing that even allows you to slide the gridline and the data
populates the table but it is cumbersome so we were hoping that Excel could
repalce ost of it.
I guess not.
Thank you!

"Jon Peltier" wrote:

Element ID is 15 (xlMajorGridlines) or 16 (xlMinorGridlines) when the gridlines are
selected.
Arg1 is always 1, since gridlines are only associated with primary axes.
Arg2 is 1 for the category (X) axis or 2 for the value (Y) axis.

Since you can only select the set of gridlines, not a single one, you cannot tell
which individual gridline was selected.

You can use a MouseUp event to get the X and Y coordinates of the click. These are
in chart window coordinates, not the X and Y of the chart axis coordinates. You can
determine which is the closest date based on this, but it takes a bunch of algebraic
calculations.

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

Chip wrote:
I believe arg2 is what determines which gridline. Correct?

"Jon Peltier" wrote:


It's not a native Excel feature, but if you're clever, you can probably
work it out. You need to determine which gridline was clicked, then go
through each series to find line segments that cross this value, and
interpolate the Y value at the time in question (unless you happen to
have actual data points for each series at this time value).

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


Chip wrote:


Great articles. My question relates to gridlines. Can I click a gridline and
get all the datapoints that intersect that gridline. My chart will have time
as the x-axis with many elements graphed as lines. I would like to display
the values of all elements at a given time represented by the gridline.

"Jon Peltier" wrote:



Chris -

You can trap events in the chart. I wrote an article about this:

http://www.computorcompanion.com/LPMArticle.asp?ID=221

and I have a couple examples showing how to get values by clicking on a
plotted point:

http://peltiertech.com/Excel/Charts/...html#PointInfo

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


Chris Manning wrote:



Is it possible to get the graph coordinates (i.e. the y coordinate of a
graph) in Excel using Visual Basic coding (or other methods) and insert them
into another cell? Specifically ,what I want to do is have the user click on
a graph, and have the y-coordinate at the time of the click entered into a
cell, or, alternatively, have the user click and drag across the graph and
have the coordinates at the beginning click and at the end of the drag
entered into two seperate cells. Further, the complete set of y-coordinates
is a column of data points, not a fcn if that helps at all.



Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
How do I graph text information in Excel garrison0013 Charts and Charting in Excel 5 April 3rd 23 03:41 PM
I'm trying to get a custom graph in Excel LoriLogan Charts and Charting in Excel 1 March 7th 05 08:30 PM
how to prepare 3 dimension graph in excel Som Chatterjee Charts and Charting in Excel 2 December 8th 04 07:16 PM
How do i create a log graph in excel Mark Bennett Charts and Charting in Excel 2 December 8th 04 07:14 PM
How do I prepare a comparitive analysis graph in Excel dalvin Charts and Charting in Excel 1 December 5th 04 03:05 PM


All times are GMT +1. The time now is 04:21 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"