Minimize all excel windows except active worksheet

[Monimonika]

I would like code that would minimize (not hide!) all excel spreadsheet windows except for the active window, even if there is another window with the same workbook open.
If I have open the following windows (whether already minimized or not) within excel:

WorkbookA.xlsm:1
WorkbookA.xlsm:2
WorkbookB.xlsb:1
WorkbookB.xlsb:2
WorkbookC.xlsx

and "WorkbookA.xlsm:2" is my active window, I want to be able to have all the windows except for "WorkbookA.xlsm:2" minimized, while leaving the "WorkbookA.xlsm:2" window untouched (please do not resize the window).
Any hidden workbook windows should NOT be unhidden.

Code I have looked up so far tend to either minimize all application windows (not just excel) or only minimize excel windows based on workbook names.

Thank you in advance for any help.

[CK76]

Something like this?

Please Login or Register  to view this content.

[Winon]

Hello Monimonika,

You may also try,

Please Login or Register  to view this content.

Regards.

[Monimonika]

To CK76,
I placed your code inside a module in Personal.XLSB in order to test it out.
The windows minimize the way I want, but I keep getting a debug error on the line:

Please Login or Register  to view this content.

To Winon,
How do I get your code to run? (Have also placed inside a module in Personal.XLSB.)

[Winon]

Hello Monimonika,

As the Code indicates, it is not for a Standard Module. With the VBE open you should Click on ThisWorkbook in the list to the left of the screen, and Post the Code in there.

Once you have done that, click on any Tab and then on Minimize, you should then see only that Tab Minimized with no other Tabs visible.

Please try it, and if you still have any problems, let me know.

Regards

[CK76]

The windows minimize the way I want, but I keep getting a debug error on the line:

Can you insert below right after "For Each" line and see what comes up in immediate window when the error shows up? Along with exact error message.

Please Login or Register  to view this content.

I tested on my end without issue, and need a bit more detail.

[Monimonika]

Hi Winon,

From your description, it seems that the minimizing would be done automatically without me being able to control if I want to have it run or not in any particular instance. Sometimes I do want two or three windows to stay in view, not just only one window.
To clarify, I plan to incorporate the code I want into some existing code that already opens and arranges windows for some of my workbooks. The problem is that if other windows are open, it messes up the window arranging. That's why I wanted to minimize all other windows except the one that I would be running my own code from.

Thank you for your code, though. Much appreciated.

[Monimonika]

CK76,

I got this:

Run-time error '1004':
Unable to set the WindowState property of the Window class

[Winon]

Hello Monimonika,

Thank you for the quick response!

However,

Sometimes I do want two or three windows to stay in view, not just only one window.

I really don't know if anyone would be able to Program for your whims. LOL

Please don't get mad at me, just ponder on it for a while!

Regards.

[CK76]

Monimonika,

What does "Debug.Print win.Caption" return? I suspect that, it's not standard Excel Window.
You may need to add extra logic to skip this one. Or as temporary solution add "On Error Resume Next" (though I don't usually recommend this route unless you know that it won't cause issue else where in your code).

[Monimonika]

CK76,

Sorry, I did not have the Immediate Window displayed. Here's what prints out:

2017 Tofu Box NJ Calendar (by Monika).xlsb:2
2017 Tofu Box NJ Calendar (by Monika).xlsb:1
PERSONAL.XLSB

Is it maybe getting stuck on trying to minimize the PERSONAL.XLSB and failing? I tried assigning your code to a button so that I did not have VBA Editor open, but the results are the same.

[CK76]

Yes. PERSONAL.XLSB is the culprit. Just use another IF statement to avoid issue with it.

[Monimonika]

Thank you so much, CK76!

I added another AND to the IF condition to skip over the PERSONAL.XLSB.

Please Login or Register  to view this content.

YAY! Have a great weekend!!