Solved: Fine Tuning an Application Event

[Opv]

I have an Application event to automatically link MyStuff.XLA to all newly created workbooks so that my personal functions are available in the new workbooks. However, since Excel opens with a new workbook each time the application is run, I had to come up with another application event (before close) to test whether that workbook is still empty when the application is closed (to avoid having to deal with popup asking whether I want to save the changes). Here is that code:


Private Sub App_WorkbookBeforeClose(ByVal Wb As Workbook, Cancel As Boolean)
On Error Resume Next
If Left(Wb.Name, 4) <> "Book" Then GoTo closeIt
If Left(Wb.Name, 4) = "Book" And _
WorksheetFunction.CountA(Cells) = 0 Then
Wb.Close False
End If
On Error GoTo 0
closeIt:
End Sub

The above script does precisely what I want it to do so long as there are no other workbooks opened at the time I close the empty workbook or exit Excel. However, when one or more existing workbooks are open, when I exit Excel I receive a prompt asking whether I want to save Book1.xls (which is actually empty except for the linked references to MyStuff.xla). How would I modify the above code so that Book1.xls closes with no alert regardless of whether additional workbooks are opened at the time I exit Excel?

[CatDaddy]

Application.DisplayAlerts = False
'code
Application.DisplayAlerts = True

[Opv]

Application.DisplayAlerts = False
'code
Application.DisplayAlerts = True

Would that not prevent the alerts for any other workbooks that might be opened?

[CatDaddy]

it would, are you trying to just prevent the popup for book1?

[CatDaddy]

Private Sub App_WorkbookBeforeClose(ByVal Wb As Workbook, Cancel As Boolean)

On Error Resume Next

If Left(Wb.Name, 4) <> "Book" Then Goto closeIt

If Left(Wb.Name, 4) = "Book" And _
WorksheetFunction.CountA(Cells) = 0 Then

Wb.Close False

End If

If InStr(Wb.Name, "Book1") Then
Application.DisplayAlerts = False
Wb.Close False
Application.DisplayAlerts = True
End if

On Error Goto 0

closeIt:

End Sub

[Opv]

That solution does not appear to be working. When I click to close the window nothing happens.

[CatDaddy]

Try something like this?

If wb.Name = "Book1.xls" Then
Application.DisplayAlerts = False
Wb.Close False
Application.DisplayAlerts = True
End If

[Opv]

Thanks for the tips. I'm no expert by any stretch of the imagination. It seems to me, however, that the problem is that the BeforeClose event does not seem to be executing for more than one open workbook. The code only seems to be effecting the active workbook (which is typically not Book1).

[Bob Phillips]

It seems to me, however, that the problem is that the BeforeClose event does not seem to be executing for more than one open workbook. The code only seems to be effecting the active workbook (which is typically not Book1).

It should do, it is an application Workbook event

[Opv]

It should do, it is an application Workbook event

Yes, as far as I know it is. :) It is a Private Sub App_WorkbookBeforeClose event, and the code is run from my Personal.xls file. It works fine if the new (empty) book is the only workbook that is open and I click close, it closes with no alerts whatsoever. However, if I have one or more other workbooks open (which is typical) and I either click the X or Close Window in the task bar, I get prompted not only to save all other workbooks but also receive a prompt asking whether I want to save the empty workbook. Something about having more than one workbook open causes my code (see the original post) not to work.

[Bob Phillips]

Can you post the workbook for us to try?

[Opv]

Can you post the workbook for us to try?
The suspect code (see my original message) is in the "This Workbook" module of my Personal.xls file and not any of the workbooks I have created. Excel behaves the same regardless of which of my workbooks is open at the time I attempt to exit Excel. I've attached my Personal.xls file for what it's worth.

I know I tend to ramble so I'll restate my objective with respect to the App_BeforeClose event. I'm wanting to be able to close the empty book that is created when Excel is initially opened without receiving a prompt to save the empty file. I would like to be able to achieve that regardless of how the workbook is closed, i.e., whether I click close on that individual workbook or whether I exit Excel by clicking X in the top right corner or selecting "Close Window" in the task bar.

[Opv]

I may have stumbled onto the source of my problem. It seems that the Workbook_BeforeClose event is firing BEFORE the App_WorkbookBeforeClose event, so that's why the code in my Application Event is not preventing the workbook event from firing. I don't suppose there is a workaround for this other than testing for an empty workbook at the workbook event level?

[CatDaddy]

you could try a for each wb in application.Workbooks in your workbook close event?

[Opv]

you could try a for each wb in application.Workbooks in your workbook close event?
I tried that; however, as I said the workbook-level event is firing BEFORE the application level event. Consequently, it don't seem to matter what's in the application event.

[CatDaddy]

hmm let me look closer and see if i can come up with anything

[CatDaddy]

did you consider just putting in a module?

[Opv]

hmm let me look closer and see if i can come up with anything

Bear in mind that the issue arises when there are other workbooks opened at the time I attempt to exit Excel. If the empty workbook is the only workbook opened, the application-level event seems to work fine.

[Opv]

did you consider just putting in a module?
I may have to do that if there is no work-around at the application-level event. I am not aware of a way to change the order in which the events fire so I may have to resort to including a default workbook-level test for all new workbooks.

[CatDaddy]

maybe xld will come out of the woodwork with an awesome workaround, hes a **** of a lot smarter than i am but as far as i can see i dont see a way to override the order of events...

[Opv]

maybe xld will come out of the woodwork with an awesome workaround, hes a **** of a lot smarter than i am but as far as i can see i dont see a way to override the order of events...

It don't take much to be smarter than I. I don't think there is a way to alter the order in which the events fire. I would just like to wrap my brain around why it works perfectly when the empty workbook is the only workbook I have open but not when I have other workbooks opened at the same time.

[Opv]

I just noticed something of which I wasn't previously aware. Note the following snippet from my original code:


If Left(Wb.name, 4) = "Book" And _
WorksheetFunction.CountA(Cells) = 0 Then
Wb.Close False
End If

I just noticed that when looping through the open workbooks, the test for the workbook name works fine; however, if that workbook is NOT the active workbook then the CountA part of the criteria is counting the cells on the active workbook rather than those in the workbook matching the Wb.name criteria.

How would the WorksheetFunction.CountA(Cells) be modified to make it count the cells in the matching workbook?

[Opv]

Well, for what it's worth, I think I have come up with a work-around to my problem. First, to restate my problem: I created an application-level NewWorkbook event to automatically link all new workbooks to my XLA file so that I wouldn't have to do that manually whenever I create new workbooks. That way I have immediate access to my scripts and functions in all new workbooks. The problem that resulted, however, is that whenever I ran Excel and then proceeded to open a saved workbook then subsequently attempted to exit Excel, I received a prompt asking whether I wanted to save the empty workbook that was created when Excel was first opened. My first attempt at a solution was to create an App_WorkbookBeforeClose event that instructed the empty workbook to close without an alert. That solution worked find so long as the empty workbook was the only workbook opened at the time I exited Excel. However, for whatever reason, if one or more additional workbooks were opened at the time I attempted to exit Excel, I still received the popup alert for the empty workbook, as the Workbook-level BeforeClose event fires before then Application-level BeforeClose event. I then tried using the Workbook_BeforeClose event in a Book.xlt file so that all new workbooks had it's own code to block the alert when Excel is exited. That worked; however, at the same time it required me to respond to the "This workbook contains macros" alert each time I opened Excel, which was annoying in and of itself. So, I found myself back at square one.

As a work-around, rather than utilizing the App_WorkbookBeforeClose event or the Workbook_BeforeClose event, I expanded the original App_NewWorkBook event code to include a timer. After the new workbook is created, the timer is triggered. If I haven't used the new workbook and it still has the name "Book1.xls" (or whatever sequence) whenever the timer expires, then that workbook is activated and tested to see if it remains empty. If it is indeed empty, it is closed without an alert and whatever other workbook I might have been working on is reactivated.

I'm open to ideas if there is a better/more simple solution. At least this option, however crude, is avoiding the popup alerts.

[Paul_Hossler]

I also use Application (Excel level) events and the 'NotTheRightKind' below might work for you

I have this in my AddIn's ThisWorkbook


Option Explicit
Private XLApp As clsApp
Private Sub Workbook_AddinInstall()
If XLApp Is Nothing Then Set XLApp = New clsApp
End Sub


and this in a class module in my addin



Option Explicit
'http://support.microsoft.com/kb/213566
'http://cpearson.com/excel/AppEvent.aspx
Private WithEvents App As Application

Private Sub App_NewWorkbook(ByVal Wb As Workbook)
If NotTheRightKind(Wb) Then Exit Sub
Call CheckManualCalc(Wb)
End Sub

Private Sub Class_Initialize()
Set App = Application
End Sub

Private Sub App_WorkbookBeforeClose(ByVal Wb As Workbook, Cancel As Boolean)
If NotTheRightKind(Wb) Then Exit Sub
Application.DefaultFilePath = Wb.Path
End Sub
Private Sub App_WorkbookBeforeSave(ByVal Wb As Workbook, ByVal SaveAsUI As Boolean, Cancel As Boolean)
If NotTheRightKind(Wb) Then Exit Sub
Application.DefaultFilePath = Wb.Path
End Sub

Private Sub App_WorkbookOpen(ByVal Wb As Workbook)
If NotTheRightKind(Wb) Then Exit Sub
Application.DefaultFilePath = Wb.Path
Call CheckManualCalc(Wb)
End Sub

Private Function NotTheRightKind(ByVal Wb As Workbook) As Boolean
NotTheRightKind = True

If Wb Is ThisWorkbook Then Exit Function
If Wb.IsAddin Then Exit Function
If Windows(Wb.Name).Visible = False Then Exit Function

NotTheRightKind = False
End Function

Private Sub CheckManualCalc(ByVal Wb As Workbook)
If Not Application.Calculation = xlCalculationManual Then Exit Sub

If MsgBox("Workbook " & Wb.Name & " has Calculation Mode set to 'Manual'" & vbCrLf & vbCrLf & _
" [Yes] to set Calculation Mode set to 'Automatic'" & vbCrLf & _
" [No] to leave it set to 'Manual'", _
vbCritical + vbDefaultButton1 + vbYesNo, "Manual Calculation set") = vbYes Then
Application.Calculation = xlCalculationAutomatic
End If
End Sub



I use it to catch Manual Calculations (long story), but you could probably adapt the concept to your needs

Paul

[Opv]

I also use Application (Excel level) events and the 'NotTheRightKind' below might work for you

Paul

Thanks, I tried the application-level events and they worked so long as the empty workbook is the only workbook that is open. However, when I have other workbooks opened and attempt to close Excel, the App_WorkbookBeforeClose event doesn't fire until AFTER the Workbook_BeforeClose event, so the popup notice is generated regardless of what is in my application event code. I can't figure out what makes it work when no other workbooks are opened. There's something about having other workbooks opened that causes it not to work.

[Paul_Hossler]

AFTER the Workbook_BeforeClose event


I think it fires for every workbook that closes. That's why I ended up using the NotTheRightKind ( ) in an attempt to avoid firing for add ins, etc.

When you solve it, please post the solution so I can see what you did

Paul

[CatDaddy]

yeah im very interested in how you can get around this as well

[Opv]

I think it fires for every workbook that closes. That's why I ended up using the NotTheRightKind ( ) in an attempt to avoid firing for add ins, etc.

When you solve it, please post the solution so I can see what you did

Paul
You are correct. It does fire for every workbook that closes. I inserted some tests MsgBox notices in both the Application-level and the Workbook-level events, just to see when they fired in relation to when I clicked to exit Excel. Invariably, the workbook-level event fired first for each workbook.

I'm still doing some testing. Is there a way to distinguish when an individual workbook is closed using the Excel toolbar (without exiting Excel) as opposed to when the close event is fired as a result of exiting Excel?

[Paul_Hossler]

I may have stumbled onto the source of my problem. It seems that the Workbook_BeforeClose event is firing BEFORE the App_WorkbookBeforeClose event, so that's why the code in my Application Event is not preventing the workbook event from firing. I don't suppose there is a workaround for this other than testing for an empty workbook at the workbook event level?


If the WB has a Workbook_BeforeClose event, it does fire first.

If you have new blank WB, how is there a Workbook_BeforeClose event in that WB?

You might be able to use something like


Application.EnableEvents = False
ActiveWorkbook.Save
Application.EnableEvents = True


to avoid triggering events that you don't want, but I don't think even XLD at his most guru-ness will be able to change the event order

Keep us posted

Paul

[Opv]

If the WB has a Workbook_BeforeClose event, it does fire first.

If you have new blank WB, how is there a Workbook_BeforeClose event in that WB?

You might be able to use something like


Application.EnableEvents = False
ActiveWorkbook.Save
Application.EnableEvents = True

to avoid triggering events that you don't want, but I don't think even XLD at his most guru-ness will be able to change the event order

Keep us posted

Paul
You are again correct that the newly created workbook does not have script in a Workbook_BeforeClose sub. I misspoke by implying that it does. The point I was attempting to make is that something is causing the popup notice asking if I want to save the empty workbook whenever there are other workbooks opened at the time I attempt to exit Excel; whereas, if the empty workbook is the only workbook open at the time, it seems to close fine without an alert. And, while there is indeed no Workbook_BeforeClose code in the empty workbook, the workbook has technically been changed by virtue of the fact that the reference links to my XLA file have been added by the NewWorkbook code. Consequently, something is telling Excel that the workbook has been changed and needs to be saved, and that something is not receiving the needed response when multiple workbooks are opened, but does for some reason when there are no other workbooks opened. It's driving me crazy.

I've tried Application.EnableEvents as well as Application.DisplayAlerts and I still can't get it to work when more than one workbook is open. I'll keep you posted. Thanks for your insight and advice.

[Opv]

EUREKA!!!!!! By golly, I think it's working. I simplified my code and it's worked flawlessly so far....keeping my fingers crossed. Here's the simplified version of the code:


Private Sub App_WorkbookBeforeClose(ByVal Wb As Workbook, Cancel As Boolean)

If Left(Wb.Name, 4) = "Book" Then

Wb.Saved = True

End If

End Sub

Now, why in the world the above works but otherwise acts quirky when I add Wb.Close False, I have no idea. But, for now I happier than a hog eattin' slop. Thanks to everyone for the help.