alanpong
asked on
Can I capture Application.WorkBookAfterClose event?
I'm using excel 97 in win95.
I see that most of commandbar buttons are disable after all workbooks are closed. However, my customized commandbar buttons are not.
I know how to deal with Application.WorkBookBefore Close event. However, even this event is triggered, the workbook is not necessary to close since the closing action can be cancel by user. So, is there something like WorkBookAfterClose event?
Thanks.
I see that most of commandbar buttons are disable after all workbooks are closed. However, my customized commandbar buttons are not.
I know how to deal with Application.WorkBookBefore
Thanks.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Add a class module to your toolbar app and paste the following code:
[code start]==================== ========== =====
Option Explicit
Public WithEvents App As Application
Private Sub App_NewWorkbook(ByVal Wb As Workbook)
Debug.Print "New book created: " & Wb.Name
End Sub
Private Sub App_WorkbookBeforeClose(By Val Wb As Workbook, Cancel As Boolean)
Debug.Print "Book closed: " & Wb.Name
Debug.Print "Count: " & Workbooks.Count
If Workbooks.Count = iCount Then MsgBox "All workbooks are closed!", vbInformation
End Sub
[code end]====================== ========== =====
Now add the following code to an existing (or new) module:
[code start]==================== ========== =====
Dim clsEvents As New EventClassModule
Public iCount As Integer
Sub InitializeApp()
Set clsEvents.App = Application
iCount = Workbooks.Count + 1
Debug.Print "Start:" & iCount
End Sub
[code end]====================== ========== =====
Run the sub InitializeApp. Do this also on the startup of your toolbar app.
Now open and close some books. The immediate window will display the status and a message box will show when all workbooks are closed.
D'Mzzl!
RoverM
[code start]====================
Option Explicit
Public WithEvents App As Application
Private Sub App_NewWorkbook(ByVal Wb As Workbook)
Debug.Print "New book created: " & Wb.Name
End Sub
Private Sub App_WorkbookBeforeClose(By
Debug.Print "Book closed: " & Wb.Name
Debug.Print "Count: " & Workbooks.Count
If Workbooks.Count = iCount Then MsgBox "All workbooks are closed!", vbInformation
End Sub
[code end]======================
Now add the following code to an existing (or new) module:
[code start]====================
Dim clsEvents As New EventClassModule
Public iCount As Integer
Sub InitializeApp()
Set clsEvents.App = Application
iCount = Workbooks.Count + 1
Debug.Print "Start:" & iCount
End Sub
[code end]======================
Run the sub InitializeApp. Do this also on the startup of your toolbar app.
Now open and close some books. The immediate window will display the status and a message box will show when all workbooks are closed.
D'Mzzl!
RoverM
btw: I named the class "EventClassModule" and referenced it this way "Dim clsEvents As New EventClassModule". You can change the name of course, as long as you do it in both places.
Have I got the wrong end of the stick? If I put a beforeclose macro in any Excel book such as
Private Sub Workbook_BeforeClose(Cance l As Boolean)
Stop
End Sub
then try to close the workbook in any way, and then Cancel. the next time I try to close (or exit) the macro is still invoked, however many times you try
Private Sub Workbook_BeforeClose(Cance
Stop
End Sub
then try to close the workbook in any way, and then Cancel. the next time I try to close (or exit) the macro is still invoked, however many times you try
macbone2:
Then you have to add that to every workbook...
I think alanpong wants their toolbar to dis/appear whenever all books are closed, whatever books were opened.
Or am I wrong alanpong ?
D'Mzzl!
RoverM
Then you have to add that to every workbook...
I think alanpong wants their toolbar to dis/appear whenever all books are closed, whatever books were opened.
Or am I wrong alanpong ?
D'Mzzl!
RoverM
ASKER
RoverM,
yes, I want some toolbar buttons in my toolbar disable after all workbooks are closed.
yes, I want some toolbar buttons in my toolbar disable after all workbooks are closed.
So, did you try my code yet ?
You will see the messagebox when all books are closed. ;-)
D'Mzzl!
RoverM
You will see the messagebox when all books are closed. ;-)
D'Mzzl!
RoverM
ASKER
I choose bruintje's approach which suits my code and lower my effort.
If I start programming in the very beginning, I will choose roverm's approach.
If I start programming in the very beginning, I will choose roverm's approach.
thanks, also to the others for the good contributions to the thread
Thanks for the explanation !
Brian, you lucky b**** ;-))
D'Mzzl!
RoverM
Brian, you lucky b**** ;-))
D'Mzzl!
RoverM
btw: Don't worry about not choosing my answer... I loved researching this and now I even used it myself. ;-)
well Mark it's friday afternoon so time to share the fun
https://www.experts-exchange.com/jsp/qManageQuestion.jsp?ta=msoffice&qid=20304323
https://www.experts-exchange.com/jsp/qManageQuestion.jsp?ta=msoffice&qid=20304323
Brian: No thanks! ;-)
I love your effort to keep this ta as clean as possible, but I don't want free points.
Really appreciate the offer, but thanks! You deserved them here!
D'Mzzl!
Mark
ps: please delete that question, I will not comment in it so you *can* delete it.
I love your effort to keep this ta as clean as possible, but I don't want free points.
Really appreciate the offer, but thanks! You deserved them here!
D'Mzzl!
Mark
ps: please delete that question, I will not comment in it so you *can* delete it.
hmmm.....okido
got a 500 point Excel question coming up anyway only i've got to break up the problem first to make it a bit more comprehensible
got a 500 point Excel question coming up anyway only i've got to break up the problem first to make it a bit more comprehensible
Looking forward to it! Should be easy ... ;-)))
D'Mzzl!
Mark
D'Mzzl!
Mark
ASKER
From spy++, other commandbar does accept some message when a workbook is closed:
<00001> 00000530 S WM_COMMAND wNotifyCode:EN_UPDATE ...
<00002> 00000530 R WM_COMMAND
<00003> 00000530 S WM_COMMAND wNotifyCode:EN_CHANGE ...
<00004> 00000530 R WM_COMMAND
However, my commandbar can't receive this message. Why?
That's there should be some after closing action done by the application. And there should be some event which the commandbar may capture.
Do you know how?