Sizing a window to fit a range

[Gary Wolf]

Help!

I'm trying to size a window to fit to the width of a selected range.
You'd think it'd be possible just to set
Window(1).Width=Range("X").Columns.Width, plus or minus some
UsableWidth adjustments, or something. But there seem to be some
differences between the Window widths, the Application widths, and the
range width that I haven't been able to reconcile.

Can anybody point me to something explaining the differences among the
different Width and UsableWidth properties for Range, Window, and
Application? For example, which include the row/column headers,
window borders, scroll bars, etc.? Which are affected by font size?
Zoom level? There must be a decent source for this.

Even better, does anyone have a good, solid routine for sizing a
window to the width of a range, given an arbitrary, fixed Zoom level,
independent of screen resolution?

Thanks once again,
-Gary Wolf

 

[Stephen Bullen]

Hi Gary,

Even better, does anyone have a good, solid routine for sizing a
window to the width of a range, given an arbitrary, fixed Zoom level,
independent of screen resolution?

I don't have one, but I imagine it would require some serious API calls
to create a 'good, solid' routine. How comfortable are you about using
them? It would probably look something like the following air-code:

1. Draw a chart over the range, activate it and delete it. This moves
an internal Excel window of class 'EXCELE' over the range
2. Use FindWindowEx to find the EXCELE window and the EXCEL7 window
that represents the workbook's window.
3. Use GetWindowRect to read their dimensions
4. Calculate the difference and convert it from pixels to points
(using GetDeviceCaps for the correct ratio), then change the window
size by that amount.

An alternative might be to change the zoom factor to fit the window,
which can be done by:

rngTheRange.Select
ActiveWindow.Zoom true


Regards

Stephen Bullen
Microsoft MVP - Excel
www.BMSLtd.ie

 

[Gary Wolf]

Thanks very much for your quick reply. You guys are great. The API
call would really be overkill in this context, but I appreciate the
advice.

FWIW, I have been hacking at this in the meantime and have come up
with the following ugly kludge to set the width of the window to the
width of the range. I haven't tested it at different screen
resolutions, but it does seem to take headings, zoom, and scroll bars
into account. It isn't perfect, but it's 'good enough'.

With Windows(1)
.Width = Range("MyRange").Width * .Zoom / 100
'...Display width has to be adjusted for zoom level.

If .DisplayHeadings Then .Width = .Width + (19.5 * .Zoom / 100) + 2.5
'...Column Heading doesn't zoom exactly as expected.

If .DisplayVerticalScrollBar Then .Width = .Width + 12
'...Scroll bar isn't affected by zoom

.Width = .Width + 8.5
'...Just a plain old fudge factor. Your guess is as good as mine.
End With

Extending this to vertical sizing is tomorrow's project. Thanks once
again!

-Gary Wolf