Macro to close two open windows of the same workbook ?

**karmapala** · 08-15-2019, 12:22 AM

I have a problem which I can't solve.

The condition is :
A. Maybe there is only one window open
B. Maybe there are two windows open (of the same workboook, but different sheet).

What I want is, when the user click X mark on the most top right of Excel application,
It will close the workbook (closing right away whether there are two opened windows or one open window).

What I've tried so far is :

Please Login or Register  to view this content.

But it doesn't work (on two windows open).
After I click the X mark,
it will close the active window first, then activate the other window.
The code on the Private Sub above is not run yet.

I need to click again the X mark, in order to close the workbook.
(then the code above is redundant).

So, my question is :
what is the code to achieve one click of the X mark,
then those two opened windows are close, closing the workbook ?

Any kind of respond would be greatly appreciated.
Thank you in advanced.

**Logit** · 08-15-2019, 12:29 AM

.
https://analysistabs.com/vba-code/workbook/m/close/

https://docs.microsoft.com/en-us/off...workbook.close

**karmapala** · 08-19-2019, 07:27 AM

Originally Posted by Logit

.
https://analysistabs.com/vba-code/workbook/m/close/

https://docs.microsoft.com/en-us/off...workbook.close

Thank you for the link, Logit.

But I am sorry, none of both codes (below) from the link you gave, work on me.

Please Login or Register  to view this content.

I still have to click the X mark (on the most top-right, not the top-right of the opened windows) two times.

Please Login or Register  to view this content.

I still have to click the X mark (on the most top-right, not the top-right of the opened windows) two times.

Thanks.

**Logit** · 08-19-2019, 11:54 AM

.
Two thoughts ...

There is more code in your project that is contributing to this issue. Without seeing the remainder of the code you are using it would be difficult to trouble shoot.

Excel has become corrupted on your computer and will require a complete reinstallation of the software.

**karmapala** · 08-24-2019, 01:55 AM

@Logit,

I am sorry, do you mean that actually Excel will directly close the app
no matter how many windows (of the same workbook) open ?

I've tried the following :
1. Make a new workbook (so there is no macro), Book1.
2. Click "VIEW" tab on the menu
3. Click "NEW WINDOW" three times.
4. Click "ARRANGE ALL" and choose "vertical".
4. Now there are 4 windows (of the same workbook) open :
Book1:1, Book1:2, Book1:3 and Book1:4

5. Click the X mark at most top-right of the Excel app.
But it doesn't close the app directly.

My first X-mark click causing Excel close the Book1:4
My second X-mark click causing Excel close the Book1:3
My third X-mark click causing Excel close the Book1:2
Then finaly on my fourth X-mark click, Excel close the app including the workbook.

There is more code in your project that is contributing to this issue

From what I did above, I guess it's not because there are more codes in my workbook.

Excel has become corrupted on your computer and will require a complete reinstallation of the software

So I think this is the culprit which causing Excel doesn't close the app (and the workbook) directly
when there is more than one window (of the same workbook) opened.

FYI, if there is only one window of the workbook,
Excel does close the app (and the workbook) directly
soon after I click the X mark on top-right of the app.

Thank you for your feedback, Logit.

**huuthang_bd** · 08-24-2019, 05:38 AM

Hi karmapala,
Your code didn't work because when you close a window of few workbook's windows, the workbook didn't close and the Workbook_BeforeClose event wasn't fired.
Maybe the Workbook_WindowDeactivate event will be helpful in this case but I'm busy and I haven't time to help you now.

**karmapala** · 08-24-2019, 05:42 PM

Hi huuthang_bd,

Originally Posted by huuthang_bd

Hi karmapala,
Your code didn't work because when you close a window of few workbook's windows, the workbook didn't close
and the Workbook_BeforeClose event wasn't fired.

That's what I've found.

If (for example) there are two windows (of the same workbook in my case),
the Workbook_BeforeClose event isn't fired on the first click, but the second click of the X mark of Excel App.

If (for example) there are four windows (of the same workbook in my case),
the Workbook_BeforeClose event isn't fired on the 1st to 3rd click, but the 4th click of the X mark of Excel App.

Maybe the Workbook_WindowDeactivate event will be helpful in this case but I'm busy and I haven't time to help you now.

Just now I've tried to change the BeforeClose to WindowDeactivate, but still the same problem.
Anyway, thank you for your explanation, huuthang_bd.
Please take your time, no need to hurry

.

**huuthang_bd** · 08-25-2019, 06:12 AM

Hi karmapala,
Now i have free time and return to help you solve this thread.
You can try this code in ThisWorkbook module.

Please Login or Register  to view this content.

**karmapala** · 08-25-2019, 11:47 PM

Hi huuthang_bd ,

I'm sorry the code still doesn't work with me.

Say, there are 3 windows of the same workbook open.
In the "ThisWorkbook" module,
I put all your codes there.
And I put a "red stop" on the "Workbook_WindowDeactivate" line.

So I click the X-mark of the Excel app.
It takes me to the module, where the yellow bar stop on my "red stop".
I push f8 to step-by-step running the code :

Please Login or Register  to view this content.

Soon after the yellow reach "End If" it goes to the "End Sub" then close whatever window which is active.
Again I click the X-mark of the Excell app, push f8 to step-by-step running the code, after the end sub, it close the active window.

It never reach to go to the below codes :

Please Login or Register  to view this content.

The same thing if I don't put the "red stopper" at all on the codes,
I still have to click the X mark n times depending on how many n of the windows open.

Actually I'm in the option whether to do :
A. open two windows of the same workbook, but showing different sheet
or
B. show a Userform (listview, as in my other thread).

But anyway, thank you once again huuthang_bd.

**huuthang_bd** · 08-26-2019, 12:10 AM

Please donn't use red stop. Just simply click X mark.

**mikerickson** · 08-26-2019, 12:18 AM

You could put this in the ThisWorkbook code module.
If you close any window of that workbook will close, no matter how many are open.

Please Login or Register  to view this content.

**huuthang_bd** · 08-26-2019, 12:22 AM

Hi mikerickson,

What happen if I only switch between windows?

**karmapala** · 08-27-2019, 02:04 PM

Originally Posted by huuthang_bd

Please donn't use red stop. Just simply click X mark.

The same thing if I don't put the "red stopper" at all on the codes,
I still have to click the X mark n times depending on how many n of the windows open, huuthang_bd.

ANyway, thank you for your help, huuthang_bd

**karmapala** · 08-27-2019, 02:31 PM

Originally Posted by mikerickson

You could put this in the ThisWorkbook code module.
If you close any window of that workbook will close, no matter how many are open.

Please Login or Register  to view this content.

It throw me error 1004 --->

First condition, there are 3 windows (of the same workbook open).
Then I paste your code to the workbook module.
I click the X-mark
it ask "Do you want to save blablabla ?"
I click the SAVE button.
The save dialog box open
I fill the name, then click OK
Then it throw me an error 1004 ---> "close method of the workbook class failed"
I click END
Then it close one of the window. (two windows left).
I click the X-mark again
It throw me the same error.
I click END.
Then it close one of the window. (one window left).
I click the X-mark again.
It throw me the same error.
I click END.
Finally the Excel app (and the workbook) close.

If I activate any of the opened window, it throw me the same error.
When the code already in the module, if I click "New Window" from the Excel VIEW menu,
it also throw me the same error.

Anyway, thank you for your respond, mike.

**dylanmoran** · 08-27-2019, 03:35 PM

Hi, i am a noob to excel but i am good at googling! see if this link helps out at all:

HTML Code:

**Greg M** · 08-27-2019, 06:19 PM

Hi there,

See if the attached workbook does what you need.

It uses the following code in a VBA Class Module called "C01_WindowEvent":

Please Login or Register  to view this content.

and the following code in the "ThisWorkbook" VBA CodeModule:

Please Login or Register  to view this content.

I opened the workbook and created several views of it in new windows. The various windows could be activated/deactivated as required, but clicking once on the "X" button on the Excel TitleBar caused all associated windows to close.

Hope this helps - please let me know how you get on.

Regards,

Greg M

**karmapala** · 08-28-2019, 07:45 AM

Hi Greg M,
Thank you, thank you very much indeed, Greg.
Your code and your sample workbook work like a dream.
Really appreciate it.

**karmapala** · 08-28-2019, 07:50 AM

Originally Posted by dylanmoran

Hi, i am a noob to excel but i am good at googling! see if this link helps out at all:

You are very right

Tip #1: Shift + Close Window Button

HTML Code:

Thank you very much, dylanmoran.

**dylanmoran** · 08-28-2019, 08:06 AM

Hope it worked/helped! Haha, you're welcome!

**Greg M** · 08-28-2019, 08:22 AM

Hi again,

Many thanks for your feedback and also for the Reputation increase - much appreciated!

You're welcome - glad I was able to help.

Best regards,

Greg M

Macro to close two open windows of the same workbook ?

LinkBack

Thread Tools

Rate This Thread

Display

Macro to close two open windows of the same workbook ?

Re: Macro to close two open windows of the same workbook ?

Re: Macro to close two open windows of the same workbook ?

Re: Macro to close two open windows of the same workbook ?

Re: Macro to close two open windows of the same workbook ?

Re: Macro to close two open windows of the same workbook ?

Re: Macro to close two open windows of the same workbook ?

Re: Macro to close two open windows of the same workbook ?

Re: Macro to close two open windows of the same workbook ?

Re: Macro to close two open windows of the same workbook ?

Re: Macro to close two open windows of the same workbook ?

Re: Macro to close two open windows of the same workbook ?

Re: Macro to close two open windows of the same workbook ?

Re: Macro to close two open windows of the same workbook ?

Re: Macro to close two open windows of the same workbook ?

Re: Macro to close two open windows of the same workbook ?

Re: Macro to close two open windows of the same workbook ?

Re: Macro to close two open windows of the same workbook ?

Re: Macro to close two open windows of the same workbook ?

Re: Macro to close two open windows of the same workbook ?

Thread Information

Users Browsing this Thread

Similar Threads

Windows task scheduler to automatically open(then close) an excel workbook

Open Workbook with windows Dialog Box & Print then close

[SOLVED] Issue with Open, copy, close Macro - when trying to close workbook Clipboard error

[SOLVED] Macro to open/close/save workbook

How to open,save and close another workbook from the current workbook using macro?

Macro to close workbook open in IE?

Macro to detect if workbook is open Getting close!

Bookmarks

Bookmarks

Posting Permissions