Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Setting Column Widths
I have a spreadsheet which has been created on another computer, and I am
having problems setting the column widths. My version of Excel 2003 is set to use a default font size of 10. It appears that the computer the sheet was created on, had a default font size of 12, because if I manually set the column width to 10 and type in "1234567890" with a font size of 12 it fills the column. Having searched this forum I found a contribution from Nick HK and tried the following code. On one of my spread sheets it worked fine - the message returned 161.25, 161.25, 1. When I ran it on the imported file it produced 207.75, 162.75, 1.27. It has set the column width to 162.75 rather than the 161.25 I was expecting. Is it possible to get it to set exactly? If not what margin of error can I expect? In this example it is quite small but with some other code I was getting 40.5 (aim) and 42 (actual) when using a factor derived from the widest column (71.25) as the factor for a narrower column. Sub test() With Worksheets(1) .Cells(5, 1).ColumnWidth = 30 Wpxl1 = .Columns(1).Width factor = 161.25 / Wpxl1 .Cells(5, 1).ColumnWidth = 30 * factor Wpxl2 = .Columns(1).Width MsgBox Wpxl1 & ", " & Wpxl2 & ", " & Wpxl1 / Wpxl2 End With End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Setting Column Widths
From the help file: One unit of column width is equal to the width of one
character in the Normal style. For proportional fonts, the width of the character 0 (zero) is used. You will not be able to set a precise column width. As you can see above, it bases the width on the zero character while in the Normal style. As you stated, XL2003 is Arial 10 for the Normal style. However, it seems to me that if you select the entire sheet of the imported file and change the font to Arial 10 the it should be compatible and allow you to set column widths as you normally would. "Gleam" wrote: I have a spreadsheet which has been created on another computer, and I am having problems setting the column widths. My version of Excel 2003 is set to use a default font size of 10. It appears that the computer the sheet was created on, had a default font size of 12, because if I manually set the column width to 10 and type in "1234567890" with a font size of 12 it fills the column. Having searched this forum I found a contribution from Nick HK and tried the following code. On one of my spread sheets it worked fine - the message returned 161.25, 161.25, 1. When I ran it on the imported file it produced 207.75, 162.75, 1.27. It has set the column width to 162.75 rather than the 161.25 I was expecting. Is it possible to get it to set exactly? If not what margin of error can I expect? In this example it is quite small but with some other code I was getting 40.5 (aim) and 42 (actual) when using a factor derived from the widest column (71.25) as the factor for a narrower column. Sub test() With Worksheets(1) .Cells(5, 1).ColumnWidth = 30 Wpxl1 = .Columns(1).Width factor = 161.25 / Wpxl1 .Cells(5, 1).ColumnWidth = 30 * factor Wpxl2 = .Columns(1).Width MsgBox Wpxl1 & ", " & Wpxl2 & ", " & Wpxl1 / Wpxl2 End With End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Setting Column Widths
Thank you for the suggestion. From the help on changing the width of a single
column (2003): "The displayed column width is the average number of digits 0 through 9 of the standard font that fit in a cell." Changing the font size in the entire sheet is not a welcome idea as many cells have been set up with different font sizes. However I tried the idea and it didn't work :( "JLGWhiz" wrote: From the help file: One unit of column width is equal to the width of one character in the Normal style. For proportional fonts, the width of the character 0 (zero) is used. You will not be able to set a precise column width. As you can see above, it bases the width on the zero character while in the Normal style. As you stated, XL2003 is Arial 10 for the Normal style. However, it seems to me that if you select the entire sheet of the imported file and change the font to Arial 10 the it should be compatible and allow you to set column widths as you normally would. "Gleam" wrote: I have a spreadsheet which has been created on another computer, and I am having problems setting the column widths. My version of Excel 2003 is set to use a default font size of 10. It appears that the computer the sheet was created on, had a default font size of 12, because if I manually set the column width to 10 and type in "1234567890" with a font size of 12 it fills the column. Having searched this forum I found a contribution from Nick HK and tried the following code. On one of my spread sheets it worked fine - the message returned 161.25, 161.25, 1. When I ran it on the imported file it produced 207.75, 162.75, 1.27. It has set the column width to 162.75 rather than the 161.25 I was expecting. Is it possible to get it to set exactly? If not what margin of error can I expect? In this example it is quite small but with some other code I was getting 40.5 (aim) and 42 (actual) when using a factor derived from the widest column (71.25) as the factor for a narrower column. Sub test() With Worksheets(1) .Cells(5, 1).ColumnWidth = 30 Wpxl1 = .Columns(1).Width factor = 161.25 / Wpxl1 .Cells(5, 1).ColumnWidth = 30 * factor Wpxl2 = .Columns(1).Width MsgBox Wpxl1 & ", " & Wpxl2 & ", " & Wpxl1 / Wpxl2 End With End Sub |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Setting Column Widths
Gleam
The number that appears in the Standard column width box is the average number of digits 0-9 of the standard font that fit in a cell. Row heights are measured in points or pixels. There are 72 points to an inch and "maybe" 96 pixels to the inch. For an interesting and enlightening discussion on this subject see http://snipurl.com/dzz8 If you want to use VBA to set height and width in mm(25.4mm/inch) for a more finite fit.................... Ole Erlandson has code for setting row and column dimensions. http://www.erlandsendata.no/english/...vbawssetrowcol Gord Dibben Excel MVP On Sat, 8 Dec 2007 00:15:00 -0800, Gleam wrote: Thank you for the suggestion. From the help on changing the width of a single column (2003): "The displayed column width is the average number of digits 0 through 9 of the standard font that fit in a cell." Changing the font size in the entire sheet is not a welcome idea as many cells have been set up with different font sizes. However I tried the idea and it didn't work :( "JLGWhiz" wrote: From the help file: One unit of column width is equal to the width of one character in the Normal style. For proportional fonts, the width of the character 0 (zero) is used. You will not be able to set a precise column width. As you can see above, it bases the width on the zero character while in the Normal style. As you stated, XL2003 is Arial 10 for the Normal style. However, it seems to me that if you select the entire sheet of the imported file and change the font to Arial 10 the it should be compatible and allow you to set column widths as you normally would. "Gleam" wrote: I have a spreadsheet which has been created on another computer, and I am having problems setting the column widths. My version of Excel 2003 is set to use a default font size of 10. It appears that the computer the sheet was created on, had a default font size of 12, because if I manually set the column width to 10 and type in "1234567890" with a font size of 12 it fills the column. Having searched this forum I found a contribution from Nick HK and tried the following code. On one of my spread sheets it worked fine - the message returned 161.25, 161.25, 1. When I ran it on the imported file it produced 207.75, 162.75, 1.27. It has set the column width to 162.75 rather than the 161.25 I was expecting. Is it possible to get it to set exactly? If not what margin of error can I expect? In this example it is quite small but with some other code I was getting 40.5 (aim) and 42 (actual) when using a factor derived from the widest column (71.25) as the factor for a narrower column. Sub test() With Worksheets(1) .Cells(5, 1).ColumnWidth = 30 Wpxl1 = .Columns(1).Width factor = 161.25 / Wpxl1 .Cells(5, 1).ColumnWidth = 30 * factor Wpxl2 = .Columns(1).Width MsgBox Wpxl1 & ", " & Wpxl2 & ", " & Wpxl1 / Wpxl2 End With End Sub |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Setting Column Widths
Gord
Thank you for this. I have tried Ole Eraldson's code on two files and on one columns B to R measure 173.5 mm and on the other 189 mm. Both have file / pageset up / adjust set to 100% normal size. At the moment this is just one rogue file amongst hundreds, so I hope no more turn up. "Gord Dibben" wrote: Gleam The number that appears in the Standard column width box is the average number of digits 0-9 of the standard font that fit in a cell. Row heights are measured in points or pixels. There are 72 points to an inch and "maybe" 96 pixels to the inch. For an interesting and enlightening discussion on this subject see http://snipurl.com/dzz8 If you want to use VBA to set height and width in mm(25.4mm/inch) for a more finite fit.................... Ole Erlandson has code for setting row and column dimensions. http://www.erlandsendata.no/english/...vbawssetrowcol Gord Dibben Excel MVP On Sat, 8 Dec 2007 00:15:00 -0800, Gleam wrote: Thank you for the suggestion. From the help on changing the width of a single column (2003): "The displayed column width is the average number of digits 0 through 9 of the standard font that fit in a cell." Changing the font size in the entire sheet is not a welcome idea as many cells have been set up with different font sizes. However I tried the idea and it didn't work :( "JLGWhiz" wrote: From the help file: One unit of column width is equal to the width of one character in the Normal style. For proportional fonts, the width of the character 0 (zero) is used. You will not be able to set a precise column width. As you can see above, it bases the width on the zero character while in the Normal style. As you stated, XL2003 is Arial 10 for the Normal style. However, it seems to me that if you select the entire sheet of the imported file and change the font to Arial 10 the it should be compatible and allow you to set column widths as you normally would. "Gleam" wrote: I have a spreadsheet which has been created on another computer, and I am having problems setting the column widths. My version of Excel 2003 is set to use a default font size of 10. It appears that the computer the sheet was created on, had a default font size of 12, because if I manually set the column width to 10 and type in "1234567890" with a font size of 12 it fills the column. Having searched this forum I found a contribution from Nick HK and tried the following code. On one of my spread sheets it worked fine - the message returned 161.25, 161.25, 1. When I ran it on the imported file it produced 207.75, 162.75, 1.27. It has set the column width to 162.75 rather than the 161.25 I was expecting. Is it possible to get it to set exactly? If not what margin of error can I expect? In this example it is quite small but with some other code I was getting 40.5 (aim) and 42 (actual) when using a factor derived from the widest column (71.25) as the factor for a narrower column. Sub test() With Worksheets(1) .Cells(5, 1).ColumnWidth = 30 Wpxl1 = .Columns(1).Width factor = 161.25 / Wpxl1 .Cells(5, 1).ColumnWidth = 30 * factor Wpxl2 = .Columns(1).Width MsgBox Wpxl1 & ", " & Wpxl2 & ", " & Wpxl1 / Wpxl2 End With End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Different widths for each column in a 100% stacked column chart | Charts and Charting in Excel | |||
Setting column widths the same on multiple worksheets | Excel Discussion (Misc queries) | |||
Setting column widths when mapping out a field trial. | Excel Worksheet Functions | |||
Setting tab widths | Excel Programming | |||
Auto setting column widths accross a whole workbook | Excel Programming |