Setting columnwidths

[Guest]

Hi all,

I've got one that has stumped me, I'm tring to set my column widths so that
they fit to the window size however when using different screen sizes and
different pixel sizes I can't get it working in all cases. I am currently
using the function application.width to grab the window size, if anyone can
help me that would be greatly appreciated.

Jason Zischke

 

[NickHK]

Jason,
Depending what you are trying to achieve, Window.UsableWidth (in points) may
be what you are after.
Then .Column.Width (in points) for each column that you wish to adjust.

NickHK

 

[Guest]

Dear NickHK

That sounds about that might work but how do do the column width in points ?

Jason

 

[NickHK]

Jason,
Something like this, converting from points to the units of ColumnWidth.
<From Help>
One unit of column width is equal to the width of one character in the
Normal style
</From Help>


Private Sub CommandButton1_Click()
Dim ColWidth As Single
Dim Factor As Single
Dim i As Long
'The range of columns you want to fit in the window
With Range("rngColumns")
ColWidth = ActiveWindow.UsableWidth / .Columns.Count
Factor = .Columns(1).Width / .Columns(1).ColumnWidth
For i = 1 To .Columns.Count
.Columns(i).ColumnWidth = ColWidth / Factor
Next
End With

End Sub

NickHK

 

[Guest]

NickHK

Thanks, that is just what I was looking for.

Jason

 

[Guest]

I'm a bit new to this. I'm using C# with interop assmblies.

It does not let me set the Width property, anywhere. Either
Application.ActiveWindow or elsewhere. I get a runtime error.

And I'm not sure as to how I convert pixels to ColumnWidth, as in
range.ColumnWidth = this.usercontrol.width (pixels);

Only the worksheet.UsedRange is visible in my application. I want this to
fit exactly into the DSO Frame I have Excel displayed in, with no scrollbars
showing.

Thanks