Selective protection of charts - permit only beforedoubleclick?
 

David -

Use chart events. If it's a chart sheet, right click on the sheet tab
and paste this into the code module:

Private Sub Chart_BeforeDoubleClick(ByVal ElementID As Long, _
ByVal Arg1 As Long, ByVal Arg2 As Long, Cancel As Boolean)
Cancel = True
MsgBox "Run the macro from here"
End Sub

Private Sub Chart_Select(ByVal ElementID As Long, _
ByVal Arg1 As Long, ByVal Arg2 As Long)
ActiveChart.Deselect
End Sub

Naturally replace the message box with something useful. If it's an
embedded chart, you need to use a class module to capture chart events,
but the code above will work in the class module. For advice on embedded
chart events, check out this link:

http://support.microsoft.com/default...b;en-us;213738

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
http://PeltierTech.com/Excel/Charts/
_______

David Powell wrote:

Is it possible to limit a user's interaction with a chart to the
double-click event (from which a macro is run)?
As far as I can make out, Excel's protection facilities are either all 'on'
or all 'off' for chart objects (apart from a .ProtectChartObject property
which addresses the chart frame itself).

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 BeforeDoubleClick event: can they be made to work
whilst we lock all control of the chart formatting?