Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Column Width Problem
I'm trying to copy column widths from a sheet in a workbook that is to
be supplied as needed by the user, to a sheet in a new workbook created by my Excel macro. In the sample workbook supplied by the user for development, I find that the destination column widths are visually much narrower than the source column widths, though the column width number is the same for both. I understand that the meaning of column width units comes from the normal font of the default style of the workbook. But again, in both cases that's the same -- Arial 10pt. (In Excel 2003, I'm looking at Format | Style | Normal | Font) Of course, I've also made sure that zoom is the same for both too -- 100%. For now, I've worked around this problem by doing a copy on the worksheet object into the new workbook, and deleting what I don't want. That gives me the same column widths. But I'd still like to know what could be causing the different column widths I was seeing. Any suggestions? Thanks, Greg |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Column Width Problem
The standard font is changed through Tools Options General tab Standard
font. Changing it only affects newly created wkbs. Your assesment as to the cause is probably correct. Regards, Greg "Greg Lovern" wrote: I'm trying to copy column widths from a sheet in a workbook that is to be supplied as needed by the user, to a sheet in a new workbook created by my Excel macro. In the sample workbook supplied by the user for development, I find that the destination column widths are visually much narrower than the source column widths, though the column width number is the same for both. I understand that the meaning of column width units comes from the normal font of the default style of the workbook. But again, in both cases that's the same -- Arial 10pt. (In Excel 2003, I'm looking at Format | Style | Normal | Font) Of course, I've also made sure that zoom is the same for both too -- 100%. For now, I've worked around this problem by doing a copy on the worksheet object into the new workbook, and deleting what I don't want. That gives me the same column widths. But I'd still like to know what could be causing the different column widths I was seeing. Any suggestions? Thanks, Greg |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Column Width Problem
On 30 Mar, 19:05, "Greg Lovern" wrote:
I'm trying to copy column widths from a sheet in a workbook that is to be supplied as needed by the user, to a sheet in a new workbook created by my Excel macro. In the sample workbook supplied by the user for development, I find that the destination column widths are visually much narrower than the source column widths, though the column width number is the same for both. I understand that the meaning of column width units comes from the normal font of the default style of the workbook. But again, in both cases that's the same -- Arial 10pt. (In Excel 2003, I'm looking at Format | Style | Normal | Font) Of course, I've also made sure that zoom is the same for both too -- 100%. For now, I've worked around this problem by doing a copy on the worksheet object into the new workbook, and deleting what I don't want. That gives me the same column widths. But I'd still like to know what could be causing the different column widths I was seeing. Any suggestions? Thanks, Greg If you do this manually, the way to carry the column width would be to click the whole column (by clicking on the column letter, say column C) and copying and pasting that. If you copied C1:C1000, the column width doesn't get carried over. In vba, try defining the column as Range("C1").entirecolumn.copy. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Column Width Problem
i'm sure there is a shorter way, but i have used something like this before.
Option Explicit Sub set_column_widths() Dim arr() As Double Dim i As Long Dim x As Long Dim z As Long x = 0 For i = 1 To 26 ReDim Preserve arr(0 To i - 1) arr(x) = Worksheets("Sheet1").Columns(i).ColumnWidth x = x + 1 Next For x = LBound(arr) To UBound(arr) Worksheets("Sheet2").Columns(x + 1).ColumnWidth = arr(x) Next End Sub -- Gary "Greg Lovern" wrote in message oups.com... I'm trying to copy column widths from a sheet in a workbook that is to be supplied as needed by the user, to a sheet in a new workbook created by my Excel macro. In the sample workbook supplied by the user for development, I find that the destination column widths are visually much narrower than the source column widths, though the column width number is the same for both. I understand that the meaning of column width units comes from the normal font of the default style of the workbook. But again, in both cases that's the same -- Arial 10pt. (In Excel 2003, I'm looking at Format | Style | Normal | Font) Of course, I've also made sure that zoom is the same for both too -- 100%. For now, I've worked around this problem by doing a copy on the worksheet object into the new workbook, and deleting what I don't want. That gives me the same column widths. But I'd still like to know what could be causing the different column widths I was seeing. Any suggestions? Thanks, Greg |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Column Width Problem
On Mar 31, 8:59 am, "jayray" wrote:
If you do this manually, the way to carry thecolumnwidth would be to click the wholecolumn(by clicking on thecolumnletter, saycolumnC) and copying and pasting that. If you copied C1:C1000, thecolumnwidthdoesn't get carried over. In vba, try defining thecolumnas Range("C1").entirecolumn.copy.- Hide quoted text - Thanks, but I've tried both of those and still have the same problem. The destination column *does* get set to the same column width *number* as the source column. The problem is that even though the two columns then have the same column width *number*, the destination column is *visually* much narrower than the source column. (and both are on 100% zoom.) Even if I manually set the destination column to the same column width number as the source column, the destination column is still visually much narrower than the source column. Both have the same default font in their normal style -- Arial 10 -- which I understand to be the way that column width units are defined. Thanks, Greg |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Column Width Problem
On Mar 31, 9:33 am, "Gary Keramidas" <GKeramidasATmsn.com wrote:
i'm sure there is a shorter way, but i have used something like this before. Option Explicit Sub set_column_widths() Dim arr() As Double Dim i As Long Dim x As Long Dim z As Long x = 0 For i = 1 To 26 ReDim Preserve arr(0 To i - 1) arr(x) = Worksheets("Sheet1").Columns(i).ColumnWidth x = x + 1 Next For x = LBound(arr) To UBound(arr) Worksheets("Sheet2").Columns(x + 1).ColumnWidth = arr(x) Next End Sub Thanks, I tried that but still getthe same problem. The destination column *does* get set to the same column width *number* as the source column. The problem is that even though the two columns then have the same column width *number*, the destination column is *visually* much narrower than the source column. (and both are on 100% zoom.) Even if I manually set the destination column to the same column width number as the source column, the destination column is still visually much narrower than the source column. Both have the same default font in their normal style -- Arial 10 -- which I understand to be the way that column width units are defined. Thanks, Greg |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Column Width Problem
Did you check screen resolution settings? That would make a visual difference.
"Greg Lovern" wrote: On Mar 31, 9:33 am, "Gary Keramidas" <GKeramidasATmsn.com wrote: i'm sure there is a shorter way, but i have used something like this before. Option Explicit Sub set_column_widths() Dim arr() As Double Dim i As Long Dim x As Long Dim z As Long x = 0 For i = 1 To 26 ReDim Preserve arr(0 To i - 1) arr(x) = Worksheets("Sheet1").Columns(i).ColumnWidth x = x + 1 Next For x = LBound(arr) To UBound(arr) Worksheets("Sheet2").Columns(x + 1).ColumnWidth = arr(x) Next End Sub Thanks, I tried that but still getthe same problem. The destination column *does* get set to the same column width *number* as the source column. The problem is that even though the two columns then have the same column width *number*, the destination column is *visually* much narrower than the source column. (and both are on 100% zoom.) Even if I manually set the destination column to the same column width number as the source column, the destination column is still visually much narrower than the source column. Both have the same default font in their normal style -- Arial 10 -- which I understand to be the way that column width units are defined. Thanks, Greg |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Column Width Problem
In case anyone is interested, I've found the problem:
The source sheet had View Formulas turned on (Tools | Options | View | Window Options | Formulas), which widens the columns. The destination sheet did not have View Formulas turned on. I hadn't noticed because that sheet didn't have any formulas. I have no idea why View Formulas was turned on in the sample provided by the user. So the reason my workaround worked was because by doing a copy on the source worksheet object into the destination workbook, I also copied over the View Formulas setting. Greg On Mar 30, 4:05 pm, "Greg Lovern" wrote: I'm trying tocopycolumnwidths from a sheet in a workbook that is to be supplied as needed by the user, to a sheet in a new workbook created by myExcelmacro. In the sample workbook supplied by the user for development, I find that the destinationcolumnwidths are visually much narrower than the sourcecolumnwidths, though thecolumnwidthnumberis the same for both. I understand that the meaning ofcolumnwidthunits comes from the normal font of thedefaultstyleof the workbook. But again, in both cases that's the same --Arial10pt. (InExcel2003, I'm looking at Format |Style| Normal | Font) Of course, I've also made sure that zoom is the same for both too -- 100%. For now, I've worked around thisproblemby doing acopyon the worksheet object into the new workbook, and deleting what I don't want. That gives me the samecolumnwidths. But I'd still like to know what could be causing the differentcolumn widths I was seeing. Any suggestions? Thanks, Greg |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Column Width Problem
yes, i was interested - thanks for reporting the solution.
additionally, anybody who searches the newsgroup in the future & finds your answer will greatly appreciate it, too! susan On Apr 6, 2:41 pm, "Greg Lovern" wrote: In case anyone is interested, I've found the problem: The source sheet had View Formulas turned on (Tools | Options | View | Window Options | Formulas), which widens the columns. The destination sheet did not have View Formulas turned on. I hadn't noticed because that sheet didn't have any formulas. I have no idea why View Formulas was turned on in the sample provided by the user. So the reason my workaround worked was because by doing a copy on the source worksheet object into the destination workbook, I also copied over the View Formulas setting. Greg On Mar 30, 4:05 pm, "Greg Lovern" wrote: I'm trying tocopycolumnwidths from a sheet in a workbook that is to be supplied as needed by the user, to a sheet in a new workbook created by myExcelmacro. In the sample workbook supplied by the user for development, I find that the destinationcolumnwidths are visually much narrower than the sourcecolumnwidths, though thecolumnwidthnumberis the same for both. I understand that the meaning ofcolumnwidthunits comes from the normal font of thedefaultstyleof the workbook. But again, in both cases that's the same --Arial10pt. (InExcel2003, I'm looking at Format |Style| Normal | Font) Of course, I've also made sure that zoom is the same for both too -- 100%. For now, I've worked around thisproblemby doing acopyon the worksheet object into the new workbook, and deleting what I don't want. That gives me the samecolumnwidths. But I'd still like to know what could be causing the differentcolumn widths I was seeing. Any suggestions? Thanks, Greg- Hide quoted text - - Show quoted text - |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Change Cell Width WITHOUT changing Column width | Excel Discussion (Misc queries) | |||
How to make cell width different than the column width it lies in | Excel Discussion (Misc queries) | |||
Set Column Width Based On Total Width Of Other Columns | Excel Programming | |||
Column Width Excel VBA Problem | Excel Programming |