ShowWindow and reposition/resize after selecting an embedded chart

[InfiniteJoy]

I have multiple worksheets, and some of those worksheets have multiple
embedded charts. I would like to have a chart open in a chart window
whenever any chart is selected. Then, I would like that chart window
to reposition itself and resize itself. I do not want to "Assign
Macro" to each chart.

My code is spread across ThisWorkbook, a module, and a class module:

ThisWorkbook
===========
Private Sub Workbook_SheetActivate(ByVal sh As Object)
Set_All_Charts
End Sub

Private Sub Workbook_SheetDeactivate(ByVal sh As Object)
Reset_All_Charts
End Sub


Module (MChartEvents)
=======

Option Explicit
Dim clsEventChart As New CEventChart
Dim clsEventCharts() As New CEventChart


Sub Set_All_Charts()
' Enable events for all charts embedded on a sheet
' Works for embedded charts on a worksheet or chart sheet
If ActiveSheet.ChartObjects.Count > 0 Then
ReDim clsEventCharts(1 To ActiveSheet.ChartObjects.Count)
Dim chtObj As ChartObject
Dim chtnum As Integer

chtnum = 1
For Each chtObj In ActiveSheet.ChartObjects
' Debug.Print chtObj.Name, chtObj.Parent.Name
Set clsEventCharts(chtnum).EvtChart = chtObj.Chart
chtnum = chtnum + 1
Next ' chtObj
End If
End Sub

Sub Reset_All_Charts()
' Disable events for all charts previously enabled together
Dim chtnum As Integer
On Error Resume Next
Set clsEventChart.EvtChart = Nothing
For chtnum = 1 To UBound(clsEventCharts)
Set clsEventCharts(chtnum).EvtChart = Nothing
Next ' chtnum
End Sub

Class Module (CEventChart)
=====================
Option Explicit

' Declare object of type "Chart" with events
Public WithEvents EvtChart As Chart

Private Sub EvtChart_Select(ByVal ElementID As Long, ByVal Arg1 As
Long, ByVal Arg2 As Long)
Application.ScreenUpdating = False
EvtChart.ShowWindow = True
' dimensions in pixels if you want to change them
With ActiveWindow
.Top = 10
.Left = 10
.Height = 440
.Width = 720
End With
Application.ScreenUpdating = True
End Sub

The results are very quirky. Sometimes it works perfectly. But mostly
I recieve a "Run-time error 1004: Unable to set the Top property of
the Window class"

If I use ActiveWindow.Top=10, I get the same problem. If I remove
..Top, it still fails on .Left.

If I use ActiveChart.ShowWindow=True, it still fails.

If I eliminate the ScreenUpdating, it still fails.

 

[InfiniteJoy]

I added a MsgBox (ActiveWindow.Top) to the Class Module and noticed
that the MsgBox was displaying before the Chart Window would actually
pop up.

So, I moved the With portion of the Class Module to its own Module and
called it as a seperate subroutine...this still results in the same
error...

 

[InfiniteJoy]

I think I have found the real root of my problem...I have set up this
chart clicking code in a workbook that has only one worksheet (called
"MainMenu"). I use controls on that worksheet to allow a user to
select other workbooks whereby additional worksheets will be copied
into this workbook. Some of the new worksheets have the embedded
charts.

Upon merely copying the new worksheets into my workbook, the chart
clicking code results in the 1004 error I described above.

However, IF I SAVE THE WORKBOOK, then my chart clicking feature works
perfectly all of a sudden...so there seems to be some kind of
linking/updating going on within Excel during the saving.

Is this a bug? Or is there some explanation as to why existing event
trapping code might not apply to worksheets that have been newly copied
to my workbook?

 

[Jon Peltier]

The chart window thing is a throwback to an ancient version of Excel,
probably the first version that allowed embedded charts in a worksheet. I'm
not surprised it misbehaves in VBA.

The event trap shouldn't care whether the sheets are newly added or original
equipment. What you might try is insert a DoEvents line after
EvtChart.ShowWindow. This allows Windows to catch up on its housekeeping,
and might let the chart window actually be created in time for you to set
its dimensions.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______