Detect that workbook is closing (within code to keep Workbook in its own Excel instance)

Hi,

I am trying to put some code into my workbook so it that will open in a new instance of Excel and not allow any other workbooks to open in that Excel instance.

I found some code which works (see below). The part where it doesn't work for me is that if I try and close my workbook, it immediately reopens it.

As per the code below, I need to try and find a way to detect that my workbook is closing. If I can do this, I will (hopefully) be able to skip out the code that reopens the spreadsheet.

I have a feeling that this will not be complicated to achieve but I can't find it (or code it).

Thanks for any help.



'~~~


Private Sub Workbook_Deactivate() 'Opens other workbooks in new Excel instance.


If "I can detect that this workbook is closing"

then

'do nothing, i.e. skip out all the code below.

Else


Dim xlApp As New Application
Dim wbPath As String
Dim wbName As String
Dim wbPathName

Set xlApp = New Excel.Application

wbPath = ActiveWorkbook.Path
wbName = ActiveWorkbook.Name
wbPathName = wbPath & "\" & wbName

ActiveWorkbook.Close

xlApp.Workbooks.Open wbPathName

xlApp.ActiveWorkbook.Windows(1).Visible = True
xlApp.Windows(1).Caption = wbName
xlApp.Visible = True
ThisWorkbook.Activate


End If



End Sub

You could create a shortcut that opens it as a new instance.

http://www.techonthenet.com/excel/qu...s/instance.php


Although, this method probably won't prevent other files from opening in that instance.

For you code, in the Workbook BeforeClose event, turn off events.

Please Login or Register  to view this content.

Also, in the future, please put code tags around your code. It makes it easier to read, select and copy.

Thanks for your e-mail but this solution is not appropriate in my case.

Also, can't see how to put tags around my code, although I found the relevant help section on this website. It just wasn't very clear to me.

Originally Posted by Whizbang

You could create a shortcut that opens it as a new instance.

http://www.techonthenet.com/excel/qu...s/instance.php


Although, this method probably won't prevent other files from opening in that instance.

For you code, in the Workbook BeforeClose event, turn off events.

Please Login or Register  to view this content.

Also, in the future, please put code tags around your code. It makes it easier to read, select and copy.

In what way is it not appropriate? Which piece of advice were you referring to, the shortcut or the BeforeClose event?

As for code tags, if you select the "Go Advanced" option on the bottom-right of the Quick Reply box, you will get all sorts of tools to help you format your text. In the toolbar, there is an option for code.

Also, you can just type this:

HTML Code: 

Hi Whizbang,

Sorry, I was referring to the shortcut solution. I thought that the "BeforeClose" solution proposed was actually part of the "shortcut" solution.

Thanks for your help. From the testing I have done it works.

Are there any implications, for setting EnableEvents = False, i.e. messages I won't see or actions that won't happen?

Also, out of interest, is there a way of detecting that the workbook is closing? I'm pretty sure my code would have worked if I could have found such code.


~~~

code test:

Please Login or Register  to view this content.

Originally Posted by Whizbang

In what way is it not appropriate? Which piece of advice were you referring to, the shortcut or the BeforeClose event?

As for code tags, if you select the "Go Advanced" option on the bottom-right of the Quick Reply box, you will get all sorts of tools to help you format your text. In the toolbar, there is an option for code.

Also, you can just type this:

HTML Code: 

Sorry for the misunderstanding.

Enable Events only affects the workbook, and since it is being closed, there is no issues with this being turned to false, unless you want something in the Deactivate event to process regardless if the workbook is being closed or not.

The BeforeClose event fires before the Deactivate event, so you could set a global variable to contain some value if the workbook is closing, then test that variable in your Deactivate event, but this seems more cumbersome and less efficient than just turning off events. If you find turning off events causes issues, though, then you'd probably have to go this route.

I have discovered a small glitch in "my code".

If my workbook is open in it's own Excel instance and then I try and open another workbook, the code works fine and opens the second work book in a separate instance of Excel.

However, if there is already a workbook open in an Excel instance and I try and open my workbook from within that Excel instance, it opens up my workbook and throws the original workbook out of that instance of Excel (prompting the user to save it) to open in a second instance.

Obviously the code in this case should simply open my workbook in a second instance.

Can anyone see how to code this?

Thanks for that Whizbang.