Compute length of a string in points depending on current font

[poptcat]

Hi.
I have to compute the length of a string in order to achieve the best
fitting inside a cell.
So given a range of merged cells or a single cell I want to resize the
range to get the whole text visible. Without using wrap or shrink.
I can't get a good aproximation for all fonts. For example, using x as
the average char, I can't write Verdana.
No need to mention I didn't find any resource on this matter.
Does anyone know how to solve it?
Gabi

 

[Helmut Weber]

Hi Gabi,

see:

http://groups.google.de/group/micro...*+author:Helmut+author:Weber#55f88076e188ebf7

Tinyurl:

http://tinyurl.com/2he88q

--

Greetings from Bavaria, Germany

Helmut Weber, MVP WordVBA

Vista Small Business, Office XP

 

[Ron Rosenfeld]

Hi.
I have to compute the length of a string in order to achieve the best
fitting inside a cell.
So given a range of merged cells or a single cell I want to resize the
range to get the whole text visible. Without using wrap or shrink.
I can't get a good aproximation for all fonts. For example, using x as
the average char, I can't write Verdana.
No need to mention I didn't find any resource on this matter.
Does anyone know how to solve it?
Gabi

You could use the AutoFit Method.
--ron

 

[poptcat]

Thanks for your response. I need a method for all Excel versions and
for all font types. I tried Autofit too.
I found something with API functions
http://www.mrexcel.com/archive2/17500/19912.htm
I'll try this.
Thanks again

 

[Dave D-C]

Helmut,
Ludo said "Thanks!" in that thread, but I didn't
get much out of it. Is that the right thread?

Gabi,
How about putting your text into an unused column,
e.g. cells(1,9), and then:
Sub Sub1()
Dim zWidth!
Columns(9).ColumnWidth = 1
Columns(9).AutoFit
zWidth = Columns(9).Width
MsgBox zWidth
End Sub
Dave D-C ------- please top-post in this group

 

[Peter T]

Unless you are sure there are no mixed font formats stick with the autofit
method (ensure there's only one cell with contents in the column you will
autofit). Note respective versions have slightly different 'padding' width.
There's also a slightly more accurate way to get the width of text by using
a textbox but quite a lot more work.

Regards,
Peter T

 

[Helmut Weber]

Sorry,

Helmut,
Ludo said "Thanks!" in that thread, but I didn't
get much out of it. Is that the right thread?

that was an answer for Word-users,
who like to find out whether text fits
into word table cells.
--

Greetings from Bavaria, Germany

Helmut Weber, MVP WordVBA

Vista Small Business, Office XP