Cell address on Excel Graph 2

[moswitch]

I'm writing a macro to act on an embedded Excel graph, I'd like to prompt the end user to pick two points on the Graph (NOT Cell) by means of an Input Box. Once they have done so empty every Cell between those two points. I can create the subroutines for everything else except the routine that allows the user to pick a point on the graph and delete the corresponding Cell value. How would I go about creating such a subroutine, your input would be greatly appreciated.

Thanks.

 

[moswitch]

By the way if I place my graph/Chart on a sheet of its own (not embedded) does that make it easier for me to access the Chart Points and manipulate the corresponding Cell?

 

[SkipVought]

Hi,

The embeddec chart do es not have events, unless you insert a Class Module

http://msdn2.microsoft.com/en-us/library/bb211434.aspx

If you use a Chart Sheet, the events are exposed. You will want to look at the MouseDown event and GetChartElement Method.

Skip,

_[red][/red]

 

[mintjulep]

The X,Y points on excel graphs are stored in arrays within the series collection for the chart. I don't think there is a direct reference back to the cells that contain the data.

So, to do what you want, I think you'll need to get the X, Y values, and do some LOOKUP, MATCH and INDEX to find the corresponding values in the underlying data.

 

[moswitch]

For someone of my experience this might be hard to do, but I'm up for the challenge. I'll try both suggestion but in the mean while if someone else as any other advice please pretty please reply to this thread/post.

Thanks again.

 

[SkipVought]

Using the GetChartElement returns 1 or 2 arguments, depending on the chart element selected, that can get you back to the surce data point.

For instance, if a series point is selected, arg 1 is the series index and arg 2 is that series' point index.

Skip,

_[red][/red]

 

[moswitch]

Thanks for the reply's guys. As I had mentioned in my original post, my intended purpose is to prompt the end user by means of an Input Box to pick two points on the chart as a range then empty the corresponding cells of that range. The problem is that once my Input Box pop-up it won't allow to click anything on the chart or choose my desired points. What am I doing wrong?

 

[SkipVought]

Instead of an InputBox, add a userform and make the Mode property modeless.

Skip,

_[red][/red]

 

[moswitch]

The following is the subroutine I'm using to acquire the Series Name, X axis, Y axis and Point. So far it works but I have no clue on how to get the Chart Point cell address automatically. Since I have the point location I'm aware I can tell my code to look on my source data sheet within my specified range for the cell in question but I'd like for my code to capture this information from my Chart Series Range/Array.

If ElementID = xlSeries Or ElementID = xlDataLabel Then

If Arg2 > 0 Then

' Extract x value from array of x values
myX = WorksheetFunction.Index _
(.SeriesCollection(Arg1).XValues, Arg2)

' Extract y value from array of y values
myY = WorksheetFunction.Index _
(.SeriesCollection(Arg1).Values, Arg2)

' Display message box with point information
MsgBox "Series " & Arg1 & vbCrLf _
& """" & .SeriesCollection(Arg1).Name & """" & vbCrLf _
& "Point " & Arg2 & vbCrLf _
& "X = " & myX & vbCrLf _
& "Y = " & myY
End If

 

[SkipVought]

Well you have the point offset (arg2) of the series (arg1).

Knowing the Series give you a reference, and knowing the point offset, gets you to that reference. The Address property of that reference is what you are looking for.

Skip,

_[red][/red]

 

[SkipVought]

To quote John Walkenbach, the Guru of Excel...

"Excel's object model has a serious flaw: There is no direct way to to use VBA to determine the ranges used in a chart."

If you wish to slog thru a work-around, creating a new class object et al, then...

http://www.j-walk.com/ss/excel/tips/tip83.htm

Skip,

_[red][/red]

 

[moswitch]

Thanks for the link I'm going to read ASAP.

 

[moswitch]

This is going to take more time than I thought......

 

[SkipVought]

...hence, the term, "slog."

Skip,

_[red][/red]