Excel 2007 Crashes on one line event macro

[Steve]

Inside the BeforeDoubleClick event on a chart, activating a range of the
worksheet will crash excel 2007. Bug does not occur for the same code in the
BeforeRightClick event of a chart, or in either event in the Excel 2010 beta.

Sample BeforeDoubleClick event on a chart:

Private Sub SpecialChartEvents_BeforeDoubleClick(ByVal ElementID As Long,
ByVal Arg1 As Long, ByVal Arg2 As Long, Cancel As Boolean)
Debug.Print "Running BeforeDoubleClick event, cancel = true!"
Cancel = True
ActiveSheet.Range("A1").Activate ' crashes excel
End Sub

Full Modules to reproduce the problem:

========================
In ThisWorkbook:
========================
Private Sub Workbook_Open()
SetupChart
End Sub

========================
In Module "GeneralStuff":
========================
Public SpecialChartsEventWrapper As New SpecialChart

Public Sub SetupChart()
DeleteCharts 'remove existing chart

' Now create our chart
Dim C As ChartObject
Set C = ActiveSheet.ChartObjects.Add(120, 20, 400, 300)
With C.Chart
.ChartType = xlColumnClustered
.SetSourceData Source:=Range("B1:B10")
.HasTitle = True
.ChartTitle.Text = "The test chart"
End With
Set SpecialChartsEventWrapper.SpecialChartEvents = C.Chart
End Sub

Public Sub DeleteCharts()
Dim x, y
For x = 1 To ActiveWorkbook.Worksheets.Count
For y = 1 To ActiveWorkbook.Worksheets(x).ChartObjects.Count
ActiveWorkbook.Worksheets(x).ChartObjects(y).Delete
Next 'chartobject
Next 'worksheet
End Sub

========================
In Class Module "SpecialChart":
========================
Option Explicit

Public WithEvents SpecialChartEvents As Chart

Private Sub SpecialChartEvents_BeforeDoubleClick(ByVal ElementID As Long,
ByVal Arg1 As Long, ByVal Arg2 As Long, Cancel As Boolean)
Debug.Print "Running BeforeDoubleClick event, cancel = true!"
Cancel = True
ActiveSheet.Range("A1").Activate ' crashes excel
End Sub

Private Sub SpecialChartEvents_BeforeRightClick(Cancel As Boolean)
Debug.Print "Running Before__RIGHT__Click event, cancel = true!"
Cancel = True
ActiveSheet.Range("A1").Activate ' crash excel?
End Sub

=================

Are there any workarounds to active a part of the worksheet in the
BeforeDoubleClick event on a chart in Excel 2007?

 

[Jan Karel Pieterse]

Hi Steve,

Private Sub SpecialChartEvents_BeforeDoubleClick(ByVal ElementID As Long,
ByVal Arg1 As Long, ByVal Arg2 As Long, Cancel As Boolean)
Debug.Print "Running BeforeDoubleClick event, cancel = true!"
Cancel = True
ActiveSheet.Range("A1").Activate ' crashes excel
End Sub

What happens if you change this to:

Private Sub SpecialChartEvents_BeforeDoubleClick(ByVal ElementID As Long,
ByVal Arg1 As Long, ByVal Arg2 As Long, Cancel As Boolean)
Debug.Print "Running BeforeDoubleClick event, cancel = true!"
Cancel = True
Application.Ontime Now(),"ActivateCell"
End Sub

and put this in a normal module:

Sub ActivateCell()
ActiveSheet.Range("A1").Activate ' crashes excel
End Sub

Regards,

Jan Karel Pieterse
Excel MVP
http://www.jkp-ads.com