ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Programming (https://www.excelbanter.com/excel-programming/)
-   -   The text length versus the cell size... (https://www.excelbanter.com/excel-programming/276808-re-text-length-versus-cell-size.html)

shockley

The text length versus the cell size...
 
Jim,

I was working on something similar today. This doesn't measure in pixels
but it would not be hard to convert:

Put the text in a cell in a column with no other contents. Use the command
"Columns(x).AutoFit" and then get the width of the column using
"Columns(x).ColumnWidth". Then change the font of the cell 1 point at a
time until you get the desired width. For more precision you could put the
string into the test cell multiple times (eg, if the string is "xyz", put in
the cell: "xyzxyzxyzxyz") and then divide the resulting column width by the
multiple used, but keep in mind that Excel (97, at least) only allows a
column width up to 255.

HTH,
Shockley

"Jim Carlock" wrote in message
...
If I want to automatically adjust the font size property for a
cell, how would I do that? I want to make the font go down
1 point at a time, compare the pixel length of the text to a
cell's pixel width.

--
Jim Carlock
http://www.microcosmotalk.com
Feel free to post back to the newsgroup!






Jim Carlock[_2_]

The text length versus the cell size...
 
I've given up on it at the moment. Some other things became a
priority.

Thanks for your comments about the AutoFit, Shockley.

--
Jim Carlock
http://www.microcosmotalk.com
Feel free to post back to the newsgroup!


"shockley" wrote in message
...
Jim,

I was working on something similar today. This doesn't measure in pixels
but it would not be hard to convert:

Put the text in a cell in a column with no other contents. Use the

command
"Columns(x).AutoFit" and then get the width of the column using
"Columns(x).ColumnWidth". Then change the font of the cell 1 point at a
time until you get the desired width. For more precision you could put

the
string into the test cell multiple times (eg, if the string is "xyz", put

in
the cell: "xyzxyzxyzxyz") and then divide the resulting column width by

the
multiple used, but keep in mind that Excel (97, at least) only allows a
column width up to 255.

HTH,
Shockley

"Jim Carlock" wrote in message
...
If I want to automatically adjust the font size property for a
cell, how would I do that? I want to make the font go down
1 point at a time, compare the pixel length of the text to a
cell's pixel width.

--
Jim Carlock
http://www.microcosmotalk.com
Feel free to post back to the newsgroup!








shockley

The text length versus the cell size...
 
Jim, glad to help. A quick and easy solution would be to enable the
ShrinkToFit property of the cell.
In Excel, with a cell or range selected, it would be Format | Cells |
Alignment | ShrinkToFit
In vba it would be Cells(x,y).ShrinkToFit = True
Shockley


"Jim Carlock" wrote in message
...
I've given up on it at the moment. Some other things became a
priority.

Thanks for your comments about the AutoFit, Shockley.

--
Jim Carlock
http://www.microcosmotalk.com
Feel free to post back to the newsgroup!


"shockley" wrote in message
...
Jim,

I was working on something similar today. This doesn't measure in

pixels
but it would not be hard to convert:

Put the text in a cell in a column with no other contents. Use the

command
"Columns(x).AutoFit" and then get the width of the column using
"Columns(x).ColumnWidth". Then change the font of the cell 1 point at a
time until you get the desired width. For more precision you could put

the
string into the test cell multiple times (eg, if the string is "xyz",

put
in
the cell: "xyzxyzxyzxyz") and then divide the resulting column width by

the
multiple used, but keep in mind that Excel (97, at least) only allows a
column width up to 255.

HTH,
Shockley

"Jim Carlock" wrote in message
...
If I want to automatically adjust the font size property for a
cell, how would I do that? I want to make the font go down
1 point at a time, compare the pixel length of the text to a
cell's pixel width.

--
Jim Carlock
http://www.microcosmotalk.com
Feel free to post back to the newsgroup!











All times are GMT +1. The time now is 11:10 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com