TextWidth (or equivalent) in VBA

[brans]

I created a seating chart in excel that has a round table in the center and seats along either side. The problem was it has to be a variable number of chairs around the table and on each side, with the chairs, and name associated with the chair, correctly spaced and angled, automatically drawn in.

This required using trig formulas and wordart. The names and chairs are all in wordart (it's the only thing I could find to write text at an angle). The table, chairs and names all draw in, flip horizontally and vertically, and space beautifully.

The problem now is that wordart adjusts the names and they are different sizes (which definitely won't do around here). I have tried putting in a static height and width based on the number of characters. That still doesn't work.

I need to know how to find out the width of the text using something like textwidth (I found that in VB) so I can choose the correct width size based on the actual size of the text and not just the character count.

I hope this makes sense.

 

[Johnomill]

Hi,

If your text is stored in a stringvariable like myLength, you can determine the number of characters with:
Len(myLenght)

Is this what you are looking for?

Best regards,

Jan

 

[brans]

The problem is the number of characters doesn't really determine how long the name will be. It is the whole "m and i" problem, with the count they are seen as the same, whereas they are definitely different sizes and wordart magnifies this problem. I guess I need the point count or something, determining how long the word is going to be exactly instead of just how many characters are in it.

Thanks

Brans

 

[brans]

I just reread my post above and wanted to make sure noone thinks that I have figured it out yet. I know what I need, but definitely not how to go about getting it.

 

[Johnomill]

Hi Brans,

I give you my thoughts about this. You can determine the number of characters, and also you can select the object. It is possible to rescale the width without changing the height.

nrChar = Len(myText)
ActiveSheet.Shapes("WordArt n&quot.Select
Selection.ShapeRange.LockAspectRatio = msoFalse
Selection.ShapeRange.Width = nrChar * yourDefault

Best regards,

Jan