PDA

View Full Version : Clicking on a Chart Point



MWE
12-03-2011, 11:27 AM
I am running xl2003 and WinXP SP3. Given a chart, you can highlight one of the plotted series and then click on a specific point. A form pops up (called "Format Data Point") which allows you to change point color, marker, etc. I wish to be able to click on a single point in a chart and have a VBA procedure fire up that will allow other things to happen to the particular data point (like change its value).

The workaround is to change one of the point attributes and then when all the changes are done run a procedure that examines the data series for those changes and take appropriate action. This should work but it is a band aid.

Any thoughts?

p45cal
12-03-2011, 04:50 PM
If you single click on a data series, single click on a point, just that point is selected, then when you hover over that point, a double headed arrow (4-headed if x-y scatter) will appear, whereupon you can drag the point and change its value.
Removed in XL2007, but reinstated in XL2010 I think.

mikerickson
12-04-2011, 12:04 PM
You can do this with chart events.
If you are working with an embeded chart, first create a class module (Class1) with this code.
Public WithEvents myChart As Chart
Public sourceRange As Range

Private Sub myChart_Select(ByVal ElementID As XlChartItem, ByVal Arg1 As Long, ByVal Arg2 As Long)
Dim dataSeries As Series
Dim uiValue As Variant
If ElementID = xlSeries And Arg2 > 0 Then
With sourceRange
uiValue = Application.InputBox("enter", Default:=.Cells(Arg2, Arg1), Type:=5)
If uiValue = False Then Exit Sub
.Cells(Arg2, Arg1) = uiValue
End With
End If
End Sub

Then put this in a normal module
Public myEventedChart As Class1

Sub test()
Set myEventedChart = New Class1
Set myEventedChart.myChart = ActiveSheet.ChartObjects("chart 1").Chart
Set myEventedChart.sourceRange = Sheet1.Range("a2:B16")
End Sub

After you run the sub test, clicking on a single data point will give you the chance to change the value.

I can't figure out how to get the data range, given a chart, so it has to be explicitly specified in Test.

In the attached, pressing the button will run Test (this has to be done every time the workbook is opened)

Press the button and select a point from the chart.

MWE
12-23-2011, 07:40 PM
If you single click on a data series, single click on a point, just that point is selected, then when you hover over that point, a double headed arrow (4-headed if x-y scatter) will appear, whereupon you can drag the point and change its value.
Removed in XL2007, but reinstated in XL2010 I think.Thanks for the reply. Sorry for the delay, I have been out of town. The two-headed arrow appears and you can move the point vertically. But when you release the mouse button, a Goal Seeking form appears with 3 arguments:
Set Cell [cell reference]
To Value [new value]
By Changing Cell [ initially blank]
The first two values are correct, i.e., the cell reference is right and the new value is about where the mouse cursor was. But it does not seem to matter what you enter in the 3rd box, you get an error "Cell Must Contain A Value". To only way out of that appears to click on the cancel button and the data point remains were it started.

I also tried converting the display to an X-Y scatter plot and playing with the 4 headed arrow. You can change the horizontal or vertical position but not both. Unfortunately, you get the same Goal Seeking form and the same problems as with the simpler case.

UPDATE: I think I have found a way to make this work. The Goal Seeking form only appears if the number of points in the series is quite small. For reasonable number of points, the approach seems to work.