How to detect whether there is a textbox in a graph in Excel using VBA?

Question

The following macro generates a textbox on top of the y-axis to show the units. I use a textbox instead of the inbuilt legend format because it's more flexible.

ActiveChart.Shapes.AddTextbox(msoTextOrientationHorizontal, 0, 0, 80, 20).Select

With Selection.ShapeRange(1).TextFrame2.TextRange
    .Characters.Text = "[Units]"
End With

I want to add a conditional before this block so that the code stops if the textbox already exists. How can I 1) count how many textboxes there are in a graph (not in the whole sheet) and 2) check whether there is a textbox of the same dimension and position in the graph?

Perhaps something like the following, but somehow restricted to textboxes?

If ActiveSheet.Shapes.Count > 0 Then
...

If ActiveSheet.Shapes(ActiveChart.Parent.Name).Count > 0 Then
...

I would loop ActiveSheet.Shapes(ActiveChart.Parent.Name) and maybe use typename to check — Nathan_Sav
– Nathan_Sav, Commented Nov 19, 2019 at 11:10
ActiveSheet.Shapes(ActiveChart.Parent.Name) works when the graph is embedded in a worksheet, not when the graph is in its own sheet. Any ideas? — Ber
– Ber, Commented Nov 19, 2019 at 15:10

Siddharth Rout · Accepted Answer · 2019-11-19 11:34:25Z

1) count how many textboxes there are in a graph (not in the whole sheet)

If the Chart is embedded in a worksheet then use this

Sub ChartInSheetHasTextBox()
    Dim ws As Worksheet
    Dim objChart As ChartObject
    Dim cht As Chart

    '~~> Change this to the relevant worksheet
    Set ws = Sheet1

    Set objChart = ws.ChartObjects(1)
    Set cht = objChart.Chart

    If cht.TextBoxes.Count > 0 Then
        MsgBox "Chart has a textbox"
    End If
End Sub

If the Chart is an independant sheet then use this

Sub ChartSheetHasTextBox()
    Dim cht As Chart
    Set cht = Charts("Chart1") '<~~ Change this to the relevant name

    If cht.TextBoxes.Count > 0 Then
        MsgBox "Chart has a textbox"
    End If
End Sub

2) check whether there is a textbox of the same dimension and position in the graph?

Simply assign it to an object and work with it

If cht.TextBoxes.Count > 0 Then
    Dim tb As TextBox
    Set tb = cht.TextBoxes(1)

    With tb
        Debug.Print tb.Width; tb.Height; tb.Top; tb.Left
    End With
End If

Collectives™ on Stack Overflow

How to detect whether there is a textbox in a graph in Excel using VBA?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related