Delete named ranges used for chart series when deleting the chart

Question

Is there any way to delete named ranges used in chart series when the chart is being deleted? I use named ranges quite extensively in my daily work, also for charting. When I create charts I often name data ranges and THEN use them for chart series.

I am looking for a way to delete USED named ranges WHEN I delete the chart. I thought about chart "delete" event, but I cannot find any info about it (does it even exist???). The second issue is how to determine which ranges have been used for chart series? Deleting the named ranges is easy, but how to actually determine, which ranges have been used in chart series?

All help is MUCH appreciated. Apologies but I cannot provide you with any code, as I have no idea how to set things up

Answer 1

Try the next code please. The USED named ranges cannot be extract directly. I used a trick to extract the ranges form SeriesCollection formula. Then compare them with names RefersToRange.Address and delete the matching name. It (now) returns a boolean value in case of match (only to see it in Immediate Window), but not necessary for your purpose. The code also delete the invalid names (having their reference lost).

Edited: I made some researches and I am afraid it is not possible to create a BeforeDelete event... It is an enumeration of events able to be created for a chart object, but this one is missing. I like to believe that I found a solution for your problem, respectively:

1. Create a class able to enable BeforeRightClick event. Name it CChartClass and write the next code:

   Option Explicit

   Public WithEvents ChartEvent As Chart

   Private Sub ChartEvent_BeforeRightClick(Cancel As Boolean) Dim msAnswer As VbMsgBoxResult msAnswer = MsgBox("Do you like to delete the active chart and its involved Named ranges?" & vbCrLf & _ " If yes, please press ""Yes"" button!", vbYesNo, "Chart deletion confirmation") If msAnswer <> vbYes Then Exit Sub Debug.Print ActiveChart.Name, ActiveChart.Parent.Name testDeleteNamesAndChart (ActiveChart.Parent.Name) End Sub

2. Create another class able to deal with workbook and worksheet events, name it CAppEvent and copy the next code:

   Option Explicit

   Public WithEvents EventApp As Excel.Application

   Private Sub EventApp_SheetActivate(ByVal Sh As Object) Set_All_Charts End Sub

   Private Sub EventApp_SheetDeactivate(ByVal Sh As Object) Reset_All_Charts End Sub

   Private Sub EventApp_WorkbookActivate(ByVal Wb As Workbook) Set_All_Charts End Sub

   Private Sub EventApp_WorkbookDeactivate(ByVal Wb As Workbook) Reset_All_Charts End Sub

3. Put the next code in a standard module (need to create a classes array in order to start the event for all existing sheet embedded charts):

Option Explicit

Dim clsAppEvent As New CAppEvent
Dim clsChartEvent As New CChartClass
Dim clsChartEvents() As New CChartClass

Sub InitializeAppEvents()
  Set clsAppEvent.EventApp = Application
  Set_All_Charts
End Sub

Sub TerminateAppEvents()
  Set clsAppEvent.EventApp = Nothing
  Reset_All_Charts
End Sub

Sub Set_All_Charts()
    If ActiveSheet.ChartObjects.Count > 0 Then
        ReDim clsChartEvents(1 To ActiveSheet.ChartObjects.Count)
        Dim chtObj As ChartObject, chtnum As Long

        chtnum = 1
        For Each chtObj In ActiveSheet.ChartObjects
            Set clsChartEvents(chtnum).ChartEvent = chtObj.Chart
            chtnum = chtnum + 1
        Next
    End If
End Sub

Sub Reset_All_Charts()
    ' Disable events for all charts
    Dim chtnum As Long
    On Error Resume Next
     Set clsChartEvent.ChartEvent = Nothing
     For chtnum = 1 To UBound(clsChartEvents)
        Set clsChartEvents(chtnum).ChartEvent = Nothing
     Next ' chtnum
    On Error GoTo 0
End Sub

Sub testDeleteNamesAndChart(strChName As String)
  Dim rng As Range, cht As Chart, sFormula As String
  Dim i As Long, j As Long, arrF As Variant, nRng As Range

  Set cht = ActiveSheet.ChartObjects(strChName).Chart
  For j = 1 To cht.SeriesCollection.Count
    sFormula = cht.SeriesCollection(j).Formula: Debug.Print sFormula
    arrF = Split(sFormula, ",")
    For i = 0 To UBound(arrF) - 1
        If i = 0 Then
            Set nRng = Range(Split((Split(sFormula, ",")(i)), "(")(1))
        Else
            Set nRng = Range(Split(sFormula, ",")(i)) '(1)
        End If
        Debug.Print nRng.Address, matchName(nRng.Address)
    Next i

  ActiveSheet.ChartObjects(strChName).Delete
End Sub

Private Function matchName(strN As String) As Boolean
   Dim Nm As Name, strTemp As String
   For Each Nm In ActiveWorkbook.Names
     On Error Resume Next
        strTemp = Nm.RefersToRange.Address
        If Err.Number <> 0 Then
            Err.Clear
            Nm.Delete
        Else
            If strN = strTemp Then
                Nm.Delete
                matchName = True: Exit Function
            End If
        End If
    On Error GoTo 0
  Next
End Function

4. Use the next events code in the ThisWorkbook module:

   Option Explicit

   Private Sub Workbook_Open() InitializeAppEvents End Sub

   Private Sub Workbook_BeforeClose(Cancel As Boolean) TerminateAppEvents End Sub

Please confirm that it worked as you need