Results 1 to 1 of 1

Embedded charts with events not working?

Threaded View

  1. #1
    Registered User
    Join Date
    04-18-2012
    Location
    The Void
    MS-Off Ver
    Excel 2007
    Posts
    1

    Embedded charts with events not working?

    I've followed the instructions found on this page:
    http://www.computorcompanion.com/LPMArticle.asp?ID=221

    Adding this code to my workbook:
    Dim sheetChart As New ChartWithEvents
    Dim embeddedCharts() As New ChartWithEvents
    Dim hasCharts As Boolean
    Private Sub Workbook_SheetActivate(ByVal aSheet As Object)
        'Enable events for all charts embedded on a sheet
        MsgBox "Activating " & aSheet.Name
        
        If TypeName(ActiveSheet) = "Chart" Then
            Set sheetChart.ch = aSheet
            MsgBox "Activating events for " & sheetChart.ch.Name
        End If
        
        If aSheet.ChartObjects.Count > 0 Then
            ReDim embeddedCharts(aSheet.ChartObjects.Count)
            Dim aChart As ChartObject
            Dim i As Integer
        
            i = 1
            For Each aChart In ActiveSheet.ChartObjects
                Set embeddedCharts(i).ch = aChart.Chart
                MsgBox "Activating events for " & embeddedCharts(i).ch.Name
                i = i + 1
            Next
            hasCharts = True
        Else
            hasCharts = False
        End If
    End Sub
    Private Sub Workbook_SheetDeactivate(ByVal aSheet As Object)
        'Disable events for all charts embedded on a sheet
        MsgBox "Deactivating " & aSheet.Name
        If Not sheetChart.ch Is Nothing Then
            MsgBox "Deactivating events for " & sheetChart.ch.Name
        End If
        Set sheetChart.ch = Nothing
        If hasCharts Then
            For i = 1 To UBound(embeddedCharts)
                If Not embeddedCharts(i).ch Is Nothing Then
                    MsgBox "Deactivating events for " & embeddedCharts(i).ch.Name
                End If
                Set embeddedCharts(i).ch = Nothing
            Next
        End If
    End Sub
    And added a class 'ChartWithEvents' containing this code:
    Public WithEvents ch As Chart
    
    Private Sub ChartWithEvents_Select(ByVal ElementID As Long, ByVal Arg1 As Long, ByVal Arg2 As Long)
        MsgBox "Select"
        If ElementID = xlSeries Then
            If Arg2 > 0 Then
                MsgBox ch.Name & ": " & "S" & Arg1 & "P" & Arg2
            End If
        End If
    End Sub
    However, when I click on a point, I don't get the message box. Nor do I get the message box 'Select'. I do get the message boxes when activating and deactivating a worksheet (and supposedly activating/deactivating events).

    Why is 'ChartWithEvents_Select' not working?

    Also, how come I don't get 'Workbook_SheetActivate' when I first open the Excel workbook? And how do I trigger on this occassion to do proper setup?
    Last edited by Lindahl; 04-18-2012 at 12:59 PM.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1