Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Setting column width
He everyone.
After entering text in columns B,C,E, I want to set the width of column D so that the sum of columns B:E = 4 inches (for printing purposes). Right now I have this code: Columns("B:C").EntireColumn.AutoFit Columns("E").EntireColumn.AutoFit TempCW = ActiveWindow.PointsToScreenPixelsX(Columns("B:C"). Width + Columns("E:E").Width) 'Columns("D").ColumnWidth = ActiveWindow.PointsToScreenPixelsX(Application.Inc hesToPoints(4) - Round(TempCW)) Right now, column D ends up way too wide. I think I might be getting mixed up because there are inches, pixels, and .ColumnWidth (which doesn't equal anything else). NOTE: I remember seeing something in the help section about resolution. This macro will be run on different computers with different resolutions. One last thing. The firewall at work won't run Java, so I am submitting this during my lunch hour at home. Could you e-mail any responses to me at work: . Thanks, Brad E. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Setting column width
Columnwidth is not set in inches, pixels or points
From help on Columnwidth: 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. I suspect there are many pixels in one unit of columnwidth Jon Peltier responded thusly to a question of similar ilk: Hi Mike - ...ColumnWidth works in zeros, that is, the number of zero characters of the default font. Most everything else goes by points. I've found the conversion to be 0.75 points per pixel, 72 points per inch/96 pixels per inch (96/inch is a standard pixel resolution). Different Windows settings may affect this: I read recently that using large fonts changes this to 0.8. There are a couple of activewindow properties, PointsToScreenPixelsX and PointsToScreenPixelsY, which tell you the dimensions of an object in pixels, but I've never had it return any values other than zero. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTech.com/ _______ -------------------- Actually I found PointsToScreenPixelX and Y would return values if the workbook window were maximized, but zero otherwise. Nonetheless, it was unclear what value they were returning as it differed based on whether the application window was maximized or not (for the same cell). -- Regards, Tom Ogilvy "Brad E." <Brad wrote in message ... He everyone. After entering text in columns B,C,E, I want to set the width of column D so that the sum of columns B:E = 4 inches (for printing purposes). Right now I have this code: Columns("B:C").EntireColumn.AutoFit Columns("E").EntireColumn.AutoFit TempCW = ActiveWindow.PointsToScreenPixelsX(Columns("B:C"). Width + Columns("E:E").Width) 'Columns("D").ColumnWidth = ActiveWindow.PointsToScreenPixelsX(Application.Inc hesToPoints(4) - Round(TempCW)) Right now, column D ends up way too wide. I think I might be getting mixed up because there are inches, pixels, and .ColumnWidth (which doesn't equal anything else). NOTE: I remember seeing something in the help section about resolution. This macro will be run on different computers with different resolutions. One last thing. The firewall at work won't run Java, so I am submitting this during my lunch hour at home. Could you e-mail any responses to me at work: . Thanks, Brad E. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Setting column width
Hi Brad,
Sub D_Adjust() Dim R As Single, Unit As Single Unit = Application.InchesToPoints(1) Columns("B:C").EntireColumn.AutoFit Columns("E:E").EntireColumn.AutoFit R = Unit * 4 R = R - Columns(2).Width - Columns(3).Width - Columns(5).Width Columns("D:D").EntireColumn.ColumnWidth = SetColWidth(R / Unit, 4) End Sub Function SetColWidth(ByVal R As Double, ByVal Col As Byte) As Double Dim lr As Single Application.ScreenUpdating = False lr = Application.InchesToPoints(R) While Columns(Col).Width lr Columns(Col).ColumnWidth = Columns(Col).ColumnWidth - 0.1 Wend While Columns(Col).Width < lr Columns(Col).ColumnWidth = Columns(Col).ColumnWidth + 0.1 Wend SetColWidth = Columns(Col).ColumnWidth End Function Regards, MP "Brad E." <Brad a écrit dans le message de ... He everyone. After entering text in columns B,C,E, I want to set the width of column D so that the sum of columns B:E = 4 inches (for printing purposes). Right now I have this code: Columns("B:C").EntireColumn.AutoFit Columns("E").EntireColumn.AutoFit TempCW = ActiveWindow.PointsToScreenPixelsX(Columns("B:C"). Width + Columns("E:E").Width) 'Columns("D").ColumnWidth = ActiveWindow.PointsToScreenPixelsX(Application.Inc hesToPoints(4) - Round(TempCW)) Right now, column D ends up way too wide. I think I might be getting mixed up because there are inches, pixels, and .ColumnWidth (which doesn't equal anything else). NOTE: I remember seeing something in the help section about resolution. This macro will be run on different computers with different resolutions. One last thing. The firewall at work won't run Java, so I am submitting this during my lunch hour at home. Could you e-mail any responses to me at work: . Thanks, Brad E. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Setting column width to specific row in Excel doc ??? Thanks! | Excel Discussion (Misc queries) | |||
setting column width | Excel Discussion (Misc queries) | |||
Setting column width in a table | Excel Discussion (Misc queries) | |||
Setting column width for blank columns | Excel Discussion (Misc queries) | |||
Setting Column Width on Imported Data | Excel Discussion (Misc queries) |