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

Hi Gabi,

see:

http://groups.google.de/group/micros...f88076e188ebf7

Tinyurl:

http://tinyurl.com/2he88q

--

Greetings from Bavaria, Germany

Helmut Weber, MVP WordVBA

Vista Small Business, Office XP

On Fri, 1 Feb 2008 03:40:53 -0800 (PST), (E-Mail Removed) wrote:

>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

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

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

Helmut Weber <(E-Mail Removed)> wrote:
>Hi Gabi,
>see:
>http://groups.google.de/group/micros...f88076e188ebf7
>Tinyurl:
>http://tinyurl.com/2he88q

(E-Mail Removed) wrote:
>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

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

<(E-Mail Removed)> wrote in message
news:d4f1aaf6-6425-4b08-9296-(E-Mail Removed)...
> 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

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