Chart insert event in Excel Sheet?

Question

Hi,

How can I be notified by an event that A chart has been added in the sheet, I have tried Sheet_Change, it does not triggers when a new chart is inserted in the sheet.

Regards
Ahmad

Answer 1

Hi,

There is no explict chart add event. Best you can do is monitor selection_change event. Of course this will only fire once the recently added chart has been deselected.

You could try detecting the usage of the Insert Chart and Chart Wizard buttons.

Thisworkbook object

Private Sub Workbook_Open()

    Set g_clsEvt = New Class1
    
End Sub

Standard code module

Public g_clsEvt As Class1
Public g_lngChartCount As Long
Public Sub CheckChartObjects()

    Dim lngCount As Long
    Dim shtTemp As Worksheet
    
    For Each shtTemp In ActiveWorkbook.Worksheets
        lngCount = lngCount + shtTemp.ChartObjects.Count
    Next
    
    If lngCount > g_lngChartCount Then
        MsgBox "Additional Chart"
        g_lngChartCount = lngCount
    End If
    
End Sub

Class module, Class1

Private WithEvents m_cbrChartWiz As CommandBarButton
Private WithEvents m_cbrChartInsert As CommandBarButton

Private Sub Class_Initialize()

    Set m_cbrChartWiz = Application.CommandBars.FindControl(ID:=436)
    Set m_cbrChartInsert = Application.CommandBars.FindControl(ID:=1957)
    
End Sub


Private Sub m_cbrChartInsert_Click(ByVal Ctrl As Office.CommandBarButton, CancelDefault As Boolean)
    
    MsgBox "Insert Chart"
    Application.OnTime Now + TimeValue("00:00:01"), "CheckChartObjects"
    
End Sub


Private Sub m_cbrChartWiz_Click(ByVal Ctrl As Office.CommandBarButton, CancelDefault As Boolean)
    
    MsgBox "Wizard"
    Application.OnTime Now + TimeValue("00:00:01"), "CheckChartObjects"
    
End Sub

---

Cheers www.andypope.info