I
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.
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.