VBA returning "Null" for TextBox.Font.Size

[Leo]

Dear all;

I was wondering if anyone knows of a workaround to the following
issue. I have a chart with a few textboxes. If I ask

Dim tb As TextBox
For Each tb In ActiveChart.TextBoxes
Debug.Print tb.Name, tb.Font.Size
Next

I get

Text Box 2054 8
Text Box 2061 Null
Text Box 2062 8
Text Box 2063 8
Text Box 2064 8
Text Box 2067 8

My problem is box 2061, which returns "Null" (!)... I can see that the
box has a nice "9" font size value in the chart itself, though. Any
ideas?

I'm using Excel 2003 (11.6355.6360) SP 1

Thanks,

Leo

 

[Andy Pope]

Hi,

That would suggest that the textbox contains mixed font sizes.

Try this routine.

Sub x()

Dim sngFSize As Single
Dim lngIndex As Long

Dim tb As TextBox
For Each tb In ActiveChart.TextBoxes
If IsNull(tb.Font.Size) Then
sngFSize = 0
Debug.Print "Mixed Font ", tb.Name, "Sizes ";
For lngIndex = 1 To Len(tb.Text)
If tb.Characters(lngIndex, 1).Font.Size <> sngFSize Then
sngFSize = tb.Characters(lngIndex, 1).Font.Size
Debug.Print ","; sngFSize;
End If
Next
Debug.Print
Else
Debug.Print tb.Name, tb.Font.Size
End If
Next

End Sub

Cheers
Andy