Text box physical string length

[Evan]

Is there an easy way to get the length of a string (inches
or points) going into a text box before the AddTextbox
call?

I'm trying to center text at the centroid of closed
polylines - lots of them. Figuring character width as
~60% of the font size seems to be a hit or miss thing.
Maybe I could use Autosize and get the textbox length with
the AddTextbox call, but I'm finding it's slow. It would
likely be faster to figure it character by character once
the width vs character code is worked out.

Evan Weiner
Richland WA

 

[Tom Ogilvy]

It is not a property that is provided by built in functions in Excel VBA.
You might be able to do something with the Windows API, but I have never
attempted it.

Can't you use the horizontalalignment property of the textframe. (assume
your shape has one).

 

[Harald Staff]

Hi Evan

Inches would depend on screen resolution, zoom, ... Here's a solution for
the text width in pixels. Add a Userform1 with a Label1 on it in your
workbook:

Function MyTextWidth(sText As String, _
sFont As String, _
LFontSize As Long) As Long
With UserForm1.Label1
..AutoSize = False
..Font.Name = sFont
..Font.Size = LFontSize
..Width = 5000
..Caption = sText
DoEvents
..AutoSize = True
MyTextWidth = .Width
End With
Unload UserForm1
End Function

Sub TEST()
MsgBox MyTextWidth("Harald rules", _
"Times New Roman", 10)
MsgBox MyTextWidth("No, it's really Rumsfeldt. Just kidding.", _
"Times New Roman", 10)
End Sub

HTH. Best wishes Harald