CODEWITHSUNDEEP
excelvba
Ber
Ber

Reputation: 53

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

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
...

Upvotes: 1

Views: 522

Answers (1)

Siddharth Rout
Siddharth Rout

Reputation: 149277

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

enter image description here

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

enter image description here

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

Upvotes: 1

Related Questions