[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 292
  • Last Modified:

Delete text boxes that are located on charts

Dear Experts:

Below macro places a text box (contents are named ranges) into each and every chart of the active worksheet.

The macro runs just fine.

There is one problem: I got no idea how to DELETE ONLY these text boxes that were created by running below macro and leave all other possible text boxes (data labels) or ordinary' text boxes located outside the chart alone.

I have attached a sample file with below code integrated for your convenience.

Help is much appreciated. Thank you very much in advance.

Regards, Andreas

 SampleFileTextBoxes.xlsm
Sub TextBoxes_Add_ToCharts()

'Add text box
'You can assign the result of the AddTextbox method to a Shape object variable and manipulate its Top, Left,
'Height and Width properties 
'1. Value = Left property
'2. Value = Top property
'3. Value = Width property
'4. Value = Height property


Dim chtTemp As Chart
Dim objTemp As Object
Dim x As Integer

x = 1
Do While x <= ActiveSheet.ChartObjects.Count
Set chtTemp = ActiveSheet.ChartObjects(x).Chart
Set objTemp = chtTemp.Shapes.AddTextBox(msoTextOrientationHorizontal, 285, 171, 135, 20)

objTemp.Select
Selection.Formula = "='" & chtTemp.Parent.Parent.Name & "'!Blue_Range_TextBox_" & Format(x, "00")
x = x + 1
Loop

ActiveCell.Select

End Sub

Open in new window

0
AndreasHermle
Asked:
AndreasHermle
  • 2
1 Solution
 
Patrick MatthewsCommented:
I modified your original procedure to add a fixed name to those textboxes, which then allows us to identify them for a sub that would try to delete them (also supplied here):


Sub TextBoxes_Add_ToCharts()

'Add text box
'You can assign the result of the AddTextbox method to a Shape object variable and manipulate its Top, Left,
'Height and Width properties (relative to those properties for myChtObj).
'myChtObj.Chart.Shapes.AddTextbox(msoTextOrientationHorizontal, 30.75, 11.25, 57.75, 21#)
'1. Value = Left property
'2. Value = Top property
'3. Value = Width property
'4. Value = Height property


Dim chtTemp As Chart
Dim objTemp As Object
Dim x As Integer

x = 1
Do While x <= ActiveSheet.ChartObjects.Count
Set chtTemp = ActiveSheet.ChartObjects(x).Chart
Set objTemp = chtTemp.Shapes.AddTextBox(msoTextOrientationHorizontal, 285, 171, 135, 20)
With objTemp
    .Select
    Selection.Formula = "='" & chtTemp.Parent.Parent.Name & "'!Blue_Range_TextBox_" & Format(x, "00")
    .Name = "TextBoxes_Add_ToCharts"
End With
x = x + 1
Loop

ActiveCell.Select

End Sub


Sub KillTextBoxes()
    
    Dim cht As ChartObject
    Dim shap As Shape
    Dim Counter As Long
    
    For Each cht In ActiveSheet.ChartObjects
        For Each shap In cht.Chart.Shapes
            If shap.Name = "TextBoxes_Add_ToCharts" Then
                shap.Delete
                Counter = Counter + 1
            End If
        Next
    Next
    
    If Counter Then
        msgbox "I killed " & Counter & " TextBoxes", vbInformation, "Yahtzee!"
    Else
        msgbox "No matching TextBoxes found", vbExclamation, "Wasting my time"
    End If
    
End Sub

Open in new window

0
 
AndreasHermleAuthor Commented:
Hi Patrick,

you know that you are one of the best here, don't you!

Great job. Exactly what I wanted. Thank you very much for your professional help. I really, really appreciate it. This forum is just great.

Regards, Andreas
0
 
Patrick MatthewsCommented:
Glad to help, Andreas!
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now