none
How can I get an event when a user selects a bitmap or chart? RRS feed

Answers

  • As I mentioned picture objects, as with most Shape type objects, do not support events. If the only objective is to trap it being selected maybe you could assign a macro, but not really the same. If this is all from your COM API maybe you could implement a timer to periodically check what is currently selected.
    • Marked as answer by DavidThi808 Monday, October 15, 2018 9:11 PM
    Monday, October 15, 2018 9:08 PM
    Moderator

All replies

  • How about using the SelectionChange event for a Worksheet?
    Sunday, October 7, 2018 9:00 PM
  • Hi;

    I was unaware of these Worksheet events - thank you. Do I just assign my event handler on a SheetActivate and remove it on a SheetDeactivate?

    Or is it better to, on a NewWorkbook and WorkbookOpen event put an event handler. And then on that workbook put a handler on each existing worksheet and also on the workbook.NewSheet event?

    And do you know for certain that this will fire when selecting a picture or chart as well as changing the selected cell in the worksheet? (It would suck to implement all this and find it doesn't fire for that case.)

    thanks - dave


    What we did for the last 6 months - Made the world's coolest reporting & docgen system even more amazing

    Monday, October 8, 2018 3:10 AM
  • I suggested the worksheet object's selectionchange event because it was an available alternative to the application object's sheetselectionchange event.

    I do not know if it will meet your needs.  I'm sure you can construct a minimal test on a single worksheet -- just hook up the event to the active worksheet on a test basis.  If it doesn't meet your needs there's no reason to go further. 

    Monday, October 8, 2018 11:42 AM
  • Bad news - Workbook.SheetSelectionChange does not fire when selecting a picture or chart. It also does not fire when going back and selecting the cell on before selecting the picture/chart. It only fires on a change from one cell to another.

    Worksheet.SelectionChange doesn't seem to fire on anything. Not on a cell change and not on a change in a cell.

    Any other ideas?

    thanks - dave


    What we did for the last 6 months - Made the world's coolest reporting & docgen system even more amazing

    Friday, October 12, 2018 4:54 PM
  • You can trap chart events in your COM API thesame way you trap worsheet events. Adapt the following VBA approach

    Add a class module and name it 'clsChart', and the following code in a normal module and the class module as indicated

    '' normal module
    Option Explicit
    Private mChtEvents As clsChart
    
    Sub initChart()
    Dim chObj As ChartObject
    
        Set chObj = Worksheets("Sheet1").ChartObjects("Chart 1")
    
        Set mChtEvents = New clsChart
        Set mChtEvents.Cht = chObj.Chart
    
    End Sub
    
    Sub TermChtEvents()
        Set mChtEvents = Nothing
    End Sub
    '' end normal module
    
    '' class module "clsChart"
    ' select Cht in the mid dropdown and other events in the right
    Option Explicit
    Public WithEvents Cht As Chart
    
    Private Sub Cht_Activate()
        Debug.Print "Cht_Activate, " & Cht.Name
    End Sub
    '' end clsChart

    Not sure what objects you mean by bitmap but most Shape objects, including 'Picture' objects, do not support events. An ActiveX worksheet controls do support events, maybe an Image object might serve your needs for the bitmap.


    Saturday, October 13, 2018 10:29 AM
    Moderator
  • Hi;

    I tried this but unfortunately there's no way for me to know when the user creates a chart. So I don't have the opportunity to add an event handler to the chart. So frustratingly close but not something I can use.

    Any other possibilities?

    thanks - dave


    What we did for the last 6 months - Made the world's coolest reporting & docgen system even more amazing

    Sunday, October 14, 2018 10:36 AM
  • I think what I suggested illustrates how to trap chart events as you originally asked, but if I follow you are now seem to be asking to be asking a different question - how to trap the creation of a new chart.

    There isn't such an event, at least not for charts as chartobjects, but typically the user will do something else that triggers an event and plenty of opportunity to update things, selection change event, workbook & sheet activate events, etc.

    Sounds like you want events for all charts in all sheets in all workbooks (ie application level for your COM API), and updated on create/delete. The following might get you started - in VBA, add three class modules named clsApp, clsChart, clsWkBook and a normal module with the following

    '' normal module
    Option Explicit
    
    Private mWBevents As clsWkBook
    Private mAppEvents As clsApp
    
    Sub initAppEvents()
        Set mAppEvents = New clsApp
        Set mAppEvents.App = Application
    End Sub
    
    '' end normal module
    
    '' class named clsApp
    Option Explicit
    Private WithEvents xlApp As Excel.Application
    Private mColWBevents As Collection
    
    Public Property Set App(xl As Excel.Application)
    Dim wb As Workbook
    
        Set xlApp = xl
        Set mColWBevents = New Collection
        For Each wb In xlApp.Workbooks
            AddWB wb
        Next
    
    End Property
    
    Private Sub AddWB(wb As Workbook)
    Dim cWBevents As clsWkBook
        Set cWBevents = New clsWkBook
        Set cWBevents.Book = wb
        mColWBevents.Add cWBevents
    End Sub
    
    Private Sub RemWB(wb As Workbook)
    Dim i As Long
    Dim cWBevents As clsWkBook
    
        For Each cWBevents In mColWBevents
            i = i + 1
            If cWBevents.Book Is wb Then
                mColWBevents.Remove i
                Exit For
            End If
        Next
            
    End Sub
    
    Private Sub xlApp_NewWorkbook(ByVal wb As Workbook)
        AddWB wb
    End Sub
    
    Private Sub xlApp_WorkbookBeforeClose(ByVal wb As Workbook, Cancel As Boolean)
        RemWB wb ' needs a timer as the wb might remain open
    End Sub
    
    Private Sub xlApp_WorkbookOpen(ByVal wb As Workbook)
        AddWB wb
    End Sub
    '' end clsApp
    
    
    '' class named "clsChart"
    ' select Cht in the mid dropdown and other events in the right
    Option Explicit
    Public WithEvents Cht As Chart
    
    Private Sub Cht_Activate()
        With Cht.Parent
        Debug.Print "Cht_Activate, "; _
                    .Parent.Parent.Name & "!" & _
                    .Parent.Name & " " & _
                    .Name
        End With
    End Sub
    '' end clsChart
    
    '' class named clsWkBook
    Option Explicit
    Public WithEvents wkBook As Excel.Workbook
    Private mColCols As Collection ' collection of collections
    
    Public Property Set Book(wb As Workbook)
        Set wkBook = wb
        InitAllCharts
    End Property
    Public Property Get Book() As Workbook
        Set Book = wkBook
    End Property
    
    Private Sub InitAllCharts()
    Dim ws As Worksheet  ' or As Object to include chartobjects on chart-sheets
    
        Set mColCols = New Collection
        For Each ws In wkBook.Worksheets ' Sheets
            SheetUpdate ws
        Next
    
    End Sub
    
    Private Sub SheetUpdate(sht As Object)
    Dim col As Collection
    Dim chObj As ChartObject
    Dim chtEvents As clsChart
    Dim i As Long
    
        On Error Resume Next
            Set col = mColCols(sht.CodeName)
        On Error GoTo 0
    
        If sht.ChartObjects.Count Or Not col Is Nothing Then
    
            If col Is Nothing Then
                Set col = New Collection
                mColCols.Add col, sht.CodeName
            End If
    
            If sht.ChartObjects.Count <> col.Count Then
                For i = col.Count To 1 Step -1
                    col.Remove i
                Next
                For Each chObj In sht.ChartObjects
                    Set chtEvents = New clsChart
                    Set chtEvents.Cht = chObj.Chart
                    col.Add chtEvents
                Next
            End If
        End If
    End Sub
    
    Private Sub wkBook_SheetSelectionChange(ByVal sh As Object, ByVal Target As Range)
        SheetUpdate sh
    End Sub
    
    '' end class clsWkBook


    Sunday, October 14, 2018 3:45 PM
    Moderator
  • Wow - thank you for something that took you a fair amount of time. And that looks like it will work for charts.

    Still need something for pictures unfortunately...

    thanks - dave


    What we did for the last 6 months - Made the world's coolest reporting & docgen system even more amazing

    Sunday, October 14, 2018 9:31 PM
  • As I mentioned picture objects, as with most Shape type objects, do not support events. If the only objective is to trap it being selected maybe you could assign a macro, but not really the same. If this is all from your COM API maybe you could implement a timer to periodically check what is currently selected.
    • Marked as answer by DavidThi808 Monday, October 15, 2018 9:11 PM
    Monday, October 15, 2018 9:08 PM
    Moderator
  • Yeah, I'm thinking of doing three things:

    1. Most clicks can first verify what is selected before processing. And if what is actually selected is not what we think is selected, do nothing but we now have the right object selected so they can go again.
    2. Have a refresh button in the menu to get what's selected.
    3. Experiment with a timer that fires every second and only does something if the selected object has changed and is NOT a Range.

    thanks - dave


    What we did for the last 6 months - Made the world's coolest reporting & docgen system even more amazing

    Monday, October 15, 2018 9:11 PM