Setting worksheets not maximized to relative size

[expect_ed]

I keep multiple worksheets open in the Excel window not maximized so I can
easily click from one to another without always pulling down a menu. Often
when I open another worksheet it is sized quite a bit larger than the Excel
workspace and I have to drag the borders several times to get it to a size
where i can see the sroll bars. I would like a macro that would resize the
current worksheet to be just slightly smaller in all directions than the
Excel workspace, regardless of how large the overall Excel window currently
is (I work on dual monitors and the Excel window might be maximized or not in
either monitor, which are different sizes).

Any ideas?

Thanks in advance for your help.
ed

 

[Rick Rothstein \(MVP - VB\)]

If you keep them all maximized, you can use Ctrl+Tab to switch between your
open workbooks.

Rick

 

[expect_ed]

That really is not much help when i have 10 workbooks open and want to get to
the 5th one.
Anyone have any help on the actual request?
Thanks
ed

 

[Rick Rothstein \(MVP - VB\)]

How do you find it now? Aren't some, maybe all, the other workbook window in
the way and you have to move/minimize/whatever them to get to the one you
want? One or more quick presses of Ctrl+Tab (which can be done with one
hand, so your other hand can remain on the mouse) and you are there. Also,
if you have a sense of the order your workbooks were opened in, you can use
Shift+Ctrl+Tab (also doable with one hand) to move backward through the
workbook ordering.

Rick

 

[expect_ed]

Well, we could spend lots of posts debating the merits of work styles, but
when you get down to it I am not likely to change mine. In response to how i
do it now, I either cascade the windows or have them in positions where I can
see enough to know which window I want, particularly since I keep several
workbooks that hold frequently used utilities in the same position, like the
bottom right or upper left of the work space. I do the same thing in the
Desktop space. It works for me. Please don't waste your time extolling what
works for you. I know the alternatives available and this is the one I've
settled on.

None of this, however, is getting any closer to solving my problem. Is it
or is it not possible to have a macro that will resize the active window to
be, let's say, 1 inch inside the workspace? I'm pretty sure you can
reposition the workbook and also resize it. Just wondering if there is a way
to idetify the size of the current workspace and use those parameters to set
the workbook location and size.

Thanks in advance for any useful feedback on that idea.
ed

 

[Rick Rothstein \(MVP - VB\)]

Here are two possibilities for you to consider. If cascading the workbooks
is an acceptable solution, then you can use this macro to do that...

Sub CascadeWorkbooks()
Application.Windows.Arrange ArrangeStyle:=xlArrangeStyleCascade
End Sub

This next macro will make the active workbook (window) fill 95% of the
usable area of Excel's "worksheet display area" and center that workbook in
the "worksheet display area"...

Sub ReduceAndCenterWindow()
With ActiveWindow
.WindowState = xlNormal
.Height = 0.95 * Application.UsableHeight
.Width = 0.95 * Application.UsableWidth
.Top = (Application.UsableHeight - .Height) / 2
.Left = (Application.UsableWidth - .Width) / 2
End With
End Sub

Rick

 

[expect_ed]

Excellent, that is just what I was looking for. Thanks for the help.
ed