How to distinguish programatically created charts/shapes from all charts/shapes on the worksheet?

Question

I am looking for a way to distinguish between the charts created by my code and the charts created "manually" by the user. Using chart.name would work if I would set a specific name_prefix or something for the charts but this property is of no use to me as the names can be changed dynamically by the user. I have thought about looking at chart like at shape and change its ID, but no luck with it either. I have checked chart/shape object model and I cannot find a property, which I could use to somehow distinguish "my charts" from all charts on a sheet collection.

The general idea is that I create a chart with VBA and when user activates it, chart.activate event opens a userform. This userform should open when user activates chart created by my code and not on activating ANY chart on the sheet. I know how to do everything except how to distinguish the charts.

Any ideas how this could be done? Thanks in advance!

EDIT: I have also thought about adding some information to series names, again, prefix type of info. That would work, but once again - it could be easily changed by the end user and this is what I would like to avoid.

Answer 1

It should be good if you posted the code you use. I (only) can suppose that you activated charts events.

Please, try the next way, which should work with events activated, or not:

1. Copy the next code in a standard module. It is a Sub which will be assigned to all created charts. It can be used instead of (existing) events, or with events working:

Sub CreatedChart()
   Dim ch As Chart
   Set ch = ActiveSheet.ChartObjects(Application.Caller).Chart
   'you can call the form in discussion here...
   Select Case ch.Parent.Name
    Case "CrChart1", "CrChart2"
        MsgBox "Here you can do something in case of Chart 1 or Chart 2..."
    Case "CrChart3"
        MsgBox "Here you can do something in case of Chart 3..."
   End Select
End Sub

2. Copy the next code in a module, too. It will create charts and assign the above Sub to them:

Sub testChartsCreate()
   Dim ws As Worksheet, ch As ChartObject, i As Long
   Set ws = ActiveSheet
   For i = 1 To 3
        Set ch = ws.ChartObjects.Add(left:=1, _
                    top:=10, width:=100, height:=100)
        ch.Name = "CrChart" & i
        ws.Shapes(ch.Name).OnAction = "CreatedChart"
        ch.Chart.ChartType = xlLine
        'do here all your charts configuration...
   Next i
End Sub

3. You can identify which of all existing charts on a sheet have been created by the above code. Please, take care to also have some manually created charts, or programmatically, but not by the above code (type), which assigns that specific Sub:

Sub testIdentifCrCharts()
   Dim sh As Worksheet, ch As ChartObject, i As Long
   
   Set sh = ActiveSheet ' use here the necessary sheet
   For Each ch In sh.ChartObjects
        Debug.Print ch.Name, isCreatedChart(ch.Chart, sh)
   Next
End Sub

Private Function isCreatedChart(ch As Chart, sh As Worksheet) As Boolean
  If sh.Shapes(ch.Parent.Name).OnAction = "CreatedChart" Then
    isCreatedChart = True
  End If
End Function

I used ch As Chart like the first function parameter, for the case of Charts to be checked, not ChartObjects...

The above solution may look complicated, but it is very easy to be understood and applied, in fact.

Please, test the above suggestion and send some feedback.