Prevent closing of one workbook when two are open

Hi,

I'm trying to prevent a user from closing a perticular workbook.

Here is some important factors.

Workbook(A) = "LA-01-04-10" This is only one of 52 other but only one is open at a time.

Workbook(B) = "LA.xls" Always the same name and is always opened when one of the workbooks(A) above it opened.


When workbook (A) is opened it will then open another workbook (B) at that time the window is hidden workbook(B) So the user can only see workbook (A) I have a button on workbook(A) that unhides the window to show workbook(B) After the user completes his work in Workbook (B) they need to click another button that sorts the list and returns them to workbook(A) (Which also hides the window for workbook(B).

The problem is I have users that are trying to close workbook(B) instead of pressing the sort list button. So what I want is to prevent the user from closing workbook(B) by displaying a msgbox saying they need to press the button.

Ive tried several approches to this but I'm still having problems. The below script works if workbook(B) is active or shown. The message is displayed and the workbook will not close. However if the user is in workbook (A) and trys to close the message is displayed and both workbooks close like they are suppose to. But why is the message displayed?

One other note I should say is that not only does workbook(A)Open workbook (B) but it also closes it. Which may be why I'm having problems?

I have also included both workbooks so you can see all the code.

Thanks for any help or advice... Mike


I have this code in Thisworkbook of workbook(B)

Please Login or Register  to view this content.

Hello Realniceguy5000,

This is a very strange problem. I have tried many different approaches as well but have not been able to stop the message box from been displayed when both files close. Even setting flags in the workbooks has failed. Apparently, it has to do with how Excel closes the workbooks. Exactly what is happening and what sequence of events takes place, I can't say. Is it a bug, a snafu, or normal behaviour? It will take more research to get an answer.

Please Login or Register  to view this content.

Go, RNG!

The workbook close event fires when the workbook the code is in is about to close, so there's no point in checking activeworkbook.name to see who's closing; it's Me.Name

Hello Realniceguy,

Persistence pays off. I found a Microsoft Knowledge Base article that details why this happens.

You cannot use the Workbook_BeforeClose event to close another Excel workbook


Additional Information:

BeforeClose Event

Occurs before the project closes. If the project has changed since it was opened, but has not been saved, this event occurs before the user is prompted to save the project.

Syntax

Private Sub Project_BeforeClose(ByVal pj As MSProject.Project)

pj The project that is being closed.

Remarks

Project events do not occur when the project is embedded in another document or application.

Microsoft Project, Leith?

I had thought I went though the workbooks and added the option explicit

You did. That's what the attaboy was for.

Hello Mike,

A few months back, I wrote a macro to close other instances of Excel from VBA and decided I needed code to close workbooks within Excel. Your problem pushed me into reviving that project. This macro takes the name of the workbook to closed and closes it. If the workbook has been saved then the extension is included, but if it has been saved yet then don't add the extension to the name. Copy this code into a separate VBA module.

Please Login or Register  to view this content.

Example
This closed a workbook I had open. Change the name of the workbook to one you want to close.

Please Login or Register  to view this content.

Thank You Leith,

I'm not sure how you pulled that off but it works like a charm... Great Work...

Thanks Again: Mike

For anyone intersted:

In WorkbookA:
Thisworkbook mod

Please Login or Register  to view this content.

In MOD 1 Leiths Function Code
In MOD 2 Leiths Sub Testit

In Workbook B
Thisworkbook

Please Login or Register  to view this content.

Once again Big Thanks to Leith!!!!!!!!!

Mike