Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sizing a window to fit a range
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sizing a window to fit a range
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sizing a window to fit a range
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 Stephen Bullen wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I make a pop-up window that displays a range in Excel?? | Excel Worksheet Functions | |||
The window opens in a smaller window not full sized window. | Excel Discussion (Misc queries) | |||
self-sizing adv.filter criteria range | Excel Discussion (Misc queries) | |||
Display range of cells in popup window | Excel Programming | |||
Open a window / box showing a selected range of cells only | Excel Programming |