Determining whether a named textbox on a chart exists

[P. Dua-Brown]

Hi

I'm using Excel 2003 to generate charts using vba.

On the charts I've created I add a few textboxes that I've named:

myChart.Chart.Shapes.AddTextbox(msoTextOrientationHorizontal, 5, 280, 300,
10).Name = "footer"
myChart.Chart.Shapes.AddTextbox(msoTextOrientationHorizontal, 50, 2, 305,
32).Name = "Chart Title"

What I would like to do is create another macro which users can use to
reformat Charts. This involves checking that the named textbox exists (users
may have deleted it) and if it does position it.

I can not figure out how to do this - can anyone point me in the right
direction?

Many thanks

Preeti

 

[Tom Ogilvy]

On Error Resume next
set shp = MyChart.Chart.Shapes("footer")
On Error goto 0
if not shp is nothing then
' now position shp
shp.Top = ??
shp.Left = ??
end if

 

[preetidb]

Many thanks Tom

From your code I've managed to create the steps to deal with all three

textboxes:

On Error Resume Next
Set tb = myChart.Chart.Shapes("footer")
On Error GoTo ErrorLine
If Not tb Is Nothing Then
tb.Top = 280
tb.Left = 5
End If
Set tb = Nothing
On Error Resume Next
Set tb = myChart.Chart.Shapes("Chart Title")
On Error GoTo ErrorLine
If Not tb Is Nothing Then
tb.Top = 2
tb.Left = 50
End If
Set tb = Nothing
On Error Resume Next
Set tb = myChart.Chart.Shapes("Subtitle")
On Error GoTo ErrorLine
If Not tb Is Nothing Then
tb.Top = 34
tb.Left = 50
End If

ErrorLine:
Set tb = Nothing
End If

Would I be able to use an array to do this? Any advice would be
appreciated.

Thanks in advance
Preeti

 

[preetidb]

Hi

I've developed the above code into this:

Sub ResetTextBoxes()
Dim tbTitles As Variant
Dim tbTop As Variant
Dim tbLeft As Variant
Dim tb As Shape
Dim Counttb As Integer

tbTitles = Array("footer", "Chart Title", "Subtitle") ', "Axis Label")
tbTop = Array(280, 2, 34)
tbLeft = Array(5, 50, 50)

myChart.Chart.PlotArea.Top = 40

For Counttb = LBound(tbTitles) To UBound(tbTitles)
On Error Resume Next
Set tb = myChart.Chart.Shapes(tbTitles(Counttb))
On Error GoTo ErrorLine
If Not tb Is Nothing Then 'now position tb
tb.Top = tbTop(Counttb)
tb.Left = tbLeft(Counttb) '5
End If
Set tb = Nothing
Next

ErrorLine:
Set tb = Nothing

End Sub

Any comments/advise would be appreciated. Is this the best approach?

Many thanks
Preeti

 

[Tom Ogilvy]

My only comment would be that you error handling jumps out if there is an
error in processing an existing textbox. while I can't imagine what would
cause an error, nonetheless, jumping out on the first textbox would abandon
the remaining. I think I would just On Error resume Next that whole section
of code. If you do have problems, you will want to comment these lines out
for trouble shooting, but since you know the code is working under normal
circumstances, I don't see it as an issue for production.

Sub ResetTextBoxes()
Dim tbTitles As Variant
Dim tbTop As Variant
Dim tbLeft As Variant
Dim tb As Shape
Dim Counttb As Integer

tbTitles = Array("footer", "Chart Title", "Subtitle") ', "Axis Label")
tbTop = Array(280, 2, 34)
tbLeft = Array(5, 50, 50)

myChart.Chart.PlotArea.Top = 40

On Error Resume Next
For Counttb = LBound(tbTitles) To UBound(tbTitles)
Set tb = myChart.Chart.Shapes(tbTitles(Counttb))
If Not tb Is Nothing Then 'now position tb
tb.Top = tbTop(Counttb)
tb.Left = tbLeft(Counttb) '5
End If
Set tb = Nothing
Next
On Error goto 0
End Sub