Setting column width

[Guest]

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.InchesToPoints(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: (e-mail address removed).

Thanks, Brad E.

 

[Tom Ogilvy]

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).

 

[Michel Pierron]

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").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