Autoclose referenced workbook

[Fab]

Hi

I have a "code" workbook with a couple of macro's. My other "normal" excel files reference this "code" workbook, thus causing it to be opened when I open one of the normal workbooks.

This all works great, however, when I've closed all "normal" workbooks, the "code" workbook remains open.

Is there a way, so that the "code" workbook auto-closes when there's no open reference to it?

Thank you already for your help!

Cheers
Fab

 

[VoG]

You could store the code in your Personal workbook http://www.rondebruin.nl/personal.htm

 

[Fab]

Good idea, unfortunately, there are several people using these files. Keeping them in the personal wb makes updating / altering the code difficult.

Any other suggestions?

 

[Fab]

I was thinking, is it possible to close the worksheet every minute, if there's an error, the timer should be reset and the vba code should retry closing the sheet in 1 minute.

Is this a feasible solution? How would I implement this?

Cheers
Fab

 

[Jaafar Tribak]

You could hook the Application events in the "code workbook" to monitor the opening and closing of the client workbooks and then check if they have a reference .

 

[Fab]

Hi Jaafar

Thank you for your reply

Could you give me a hint / weblink on how to do such a thing in VBA?

Many thanks!

Cheers
Fab

 

[Jaafar Tribak]

Hi Jaafar

Thank you for your reply

Could you give me a hint / weblink on how to do such a thing in VBA?

Many thanks!

Cheers
Fab

I am sorry Fab but just as I was to start writing the code, I came accross this problem of not being able to change the VBAProject name which is required for setting a workbook reference.

I change the VBAProject name and save the workbook but when I reopen it, the new saved VBAProject name is lost and it defaults back to VBAProject !

Does anybody know why this happens ?

 

[Fab]

Could I rename the project and save the workbook for you and send it to you with a Rapidshare link?

Do you think that could work?

Cheers
Fab

 

[Jaafar Tribak]

Could I rename the project and save the workbook for you and send it to you with a Rapidshare link?

Do you think that could work?

Cheers
Fab

Yes that's fine. You can PM it to me.

 

[Fab]

I've found a solution!

After all, it was a lot easier than I thought, but I guess that's always true once you've found a way to solve a problem.

I'd like to share my solution for other people who may find themselves in the same situation.

Put this in a standard module in the workbook that will be referenced by the others:

Option Explicit
 
Public Sub ReadyToClose()
    ' Disable error handling to prevent the annoying error message that the workbook is still referenced
    On Error Resume Next
 
    Application.OnTime Now + TimeValue("0:0:1"), "selfCloseWorkbook"
 
    ' Restore error handling
    On Error GoTo 0
End Sub
 
' Actually closes this workbook
Public Sub selfCloseWorkbook()
    ThisWorkbook.Close
End Sub

This belongs into the ThisWorkbook module of the workbook that uses the reference:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
 
Call ReadyToClose
 
End Sub