Automatically Close Workbook If Another Workbook Is Opened

[knucklemonkey]

Hello and thank you for taking the time to try to help me! I have a workbook that automatically saves itself when it is closed without prompting the user. Now I need to make the workbook automatically close if another workbook is opened.

So if my_workbook.xls is opened and you open another workbook, I need my_workbook.xls to close instantly.

Please give me the secret to making this happen and I thank you again for trying to help me. I am just getting started but I learn fast!

[davesexcel]

The secret is "You Don't" but don't tell anybody

Just kidding

the code for closing a book is

Windows("Book1").Activate
ActiveWorkbook.Close


Here is a sites with a function to see if a workbook is open:

http://exceltips.vitalnews.com/Pages...eady_Open.html

But it doesn't work for me

I will keep working on it

[davesexcel]

Okay, Here it is....

The code needs to see if the workbook is open first then if it is it will close it,


so if workbook2 is open then close workbook2
you will need to place this macro in a workbook1 workbookmodule

Please Login or Register  to view this content.

This code goes in Search3 workbook,

Please Login or Register  to view this content.

Workbook Search3 has the macro when book1 opens it brings up Calls Search3 macro and checks to see if book2 is open, then if it is , it closes it

[sharkfoot]

Okay, Here it is....

The code needs to see if the workbook is open first then if it is it will close it,


so if workbook2 is open then close workbook2
you will need to place this macro in a workbook1 workbookmodule

Workbook Search3 has the macro when book1 opens it brings up Calls Search3 macro and checks to see if book2 is open, then if it is , it closes it

I have attached a file with what I think is an example of your instructions. I am likely doing something wrong here. Can you take a look. I have named the file that will be open (the one the user should never have open with any other excel file) "tango.xls". If tango.xls is open and any other workbook is opened, tango.xls should automatically close.

And thank you so much for trying to help me!

[davesexcel]

you wouls need this in the Search3 workbook module

Private Sub Workbook_Open()
Call IfOpenThenClose
End Sub


so when Search3 opens it will close tango

I am still looking for a code thatwill just autoclose when anyother workbook is open, but I can't find it yet

[davesexcel]

This sub closes all open workbooks when the workbook with the code in it is opened place this code in the workbook module, if you don't know where this is check out this site for a detailed description

http://www.contextures.com/xlvba01.html#Workbook

Please Login or Register  to view this content.

[sharkfoot]

This sub closes all open workbooks when the workbook with the code in it is opened

That's awesome! I have one more little snag to figure here though. Perhaps you have an idea.

If the workbook with this code is open and a user opens a new excel file my tango.xls doesn't close.

If any workbook is open and the user opens tango.xls, all other workbooks close (perfect!) but the reverse is not yet true. How do I accomplish this?

And thank you again for the 1st part of this!

[davesexcel]

HMMM....
not knowing a great answer for this, how about letting us know why you need the book closed anyway if another book is opened and is it really required for this other book to be closed?

And the only answer I have for you is to have the code in the books you only want to stay openned by itself..


Maybe somebody else has an idea of how one workbook can detect if another book is opened, without activating a code, but it does seem to be unneccesary to do this!