Delete TextBoxes from a selected chart

Question

The following code allows the user to delete TextBoxes from a named chart.

To select a chart, the user has to enter the chart name in the macro.

In this example, the name is "Chart xyz".

How do you build the code, so that the user can simply select a chart by clicking on it?

Sub DeleteTextBoxesFromASelectedChart()
  Dim ChrtObj As ChartObject
    On Error Resume Next
      Set ChrtObj = ActiveSheet.ChartObjects("Chart xyz")
          ChrtObj.Chart.TextBoxes.Delete
    On Error GoTo 0
End Sub

How do you say Set ChrtObj = ActiveChart?

Answer 1

• Avoid using "On Error Resume Next" unless you are certain that a specific error is being intentionally ignored.

• User may select Chart and shape. eg.

Sub DelTextBoxesOnChart()
    Dim bSelChart As Boolean
    If Not ActiveChart Is Nothing Then
        If ActiveChart.Shapes.Count > 0 Then
            ActiveChart.TextBoxes.Delete
        End If
    Else
        Select Case TypeName(Selection)
        Case "DrawingObjects"
            Dim s
            For Each s In Selection
                If TypeName(s) = "ChartObject" Then
                    bSelChart = True
                    If s.Chart.Shapes.Count > 0 Then
                        s.Chart.TextBoxes.Delete
                    End If
                End If
            Next
        Case Else
            bSelChart = False
        End Select
        If Not bSelChart Then MsgBox "Please select a Chart"
    End If
End Sub