[David Powell]

Is it possible to limit a user's interaction with an embedded chart to
the
double-click (DoubleClick) event (from which a macro would be run)?
As far as I can make out, Excel's protection facilities - despite the
various .Protect* properties of a Sheet or Chart - will always put
DoubleClicking in the same category as formatting.

If it's not directly possible, I suppose I'm interested in
workarounds, but my customer wanted to have this particular type of of
user-interaction. At present, the chart itself is all that needs to
be selected. In future releases, it would be good to be able to
select a chart data point and provide a report or 'drill-down' view.
The design works well without protection, using the chart's
BeforeDoubleClick event: can this be made to work whilst we lock all
control of the chart formatting?

[revised version of original post to
microsoft.public.excel.programming]

[Jon Peltier[_3_]]

David -

Run your macro off a single click, using this event procedure to detect
the click:

Private Sub EmbChart_MouseDown _
(ByVal Button As Long, ByVal Shift As Long, _
ByVal x As Long, ByVal y As Long)
ActiveChart.Deselect
Windows(ActiveWorkbook.Name).Activate
ActiveCell.Select
Call myMacro(EmbChart, x, y)
End Sub

This works for an embedded chart because it immediately selects the
previous active cell, without letting anything happen to the chart, but
it still can be used to determine what chart element was clicked on.
Here's a dummy macro that determines what was clicked on:

Sub myMacro(myChart As Chart, myX As Long, myY As Long)
Dim ElementID As Long, Arg1 As Long, Arg2 As Long
myChart.GetChartElement myX, myY, ElementID, Arg1, Arg2
MsgBox "You've selected chart element " & ElementID & " (" _
& Arg1 & ", " & Arg2 & ")."
End Sub

- Jon
-------
Jon Peltier, Microsoft Excel MVP
http://www.geocities.com/jonpeltier/Excel/index.html
_______

David Powell wrote:
Is it possible to limit a user's interaction with an embedded chart to
the
double-click (DoubleClick) event (from which a macro would be run)?
As far as I can make out, Excel's protection facilities - despite the
various .Protect* properties of a Sheet or Chart - will always put
DoubleClicking in the same category as formatting.

If it's not directly possible, I suppose I'm interested in
workarounds, but my customer wanted to have this particular type of of
user-interaction. At present, the chart itself is all that needs to
be selected. In future releases, it would be good to be able to
select a chart data point and provide a report or 'drill-down' view.
The design works well without protection, using the chart's
BeforeDoubleClick event: can this be made to work whilst we lock all
control of the chart formatting?

[revised version of original post to
microsoft.public.excel.programming]