Solved

Can I capture Application.WorkBookAfterClose event?

Posted on 2002-05-17
16
416 Views
Last Modified: 2008-02-01
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.WorkBookBeforeClose 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.
0
Comment
Question by:alanpong
  • 8
  • 4
  • 3
  • +1
16 Comments
 
LVL 44

Accepted Solution

by:
bruintje earned 50 total points
ID: 7017970
Hi Alanpong,

This is some snippet from a newsgroup author is John Guyen, and could be worth a try

"deleteitem" is another sub which deletes the custom menu.

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim ans As Integer
If Me.Saved = True Then
  Call deleteitem
   Exit Sub
    Else
      ans = MsgBox("Do you want to save changes to " & ActiveWorkbook.Name &
" ?", vbYesNoCancel + vbQuestion)
      If ans = vbYes Then
      ActiveWorkbook.Save
      ElseIf ans = vbNo Then
     ActiveWorkbook.Saved = True
    ElseIf ans = vbCancel Then
    Cancel = True
   Exit Sub
End If
End If
Call deleteitem
End Sub

instead of the deleteitem you can use a sub to disable the custom toolbar

:O)Bruintje
0
 
LVL 1

Author Comment

by:alanpong
ID: 7018054
Hi, Bruintje
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?
0
 
LVL 12

Expert Comment

by:roverm
ID: 7018792
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(ByVal 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
0
 
LVL 12

Expert Comment

by:roverm
ID: 7018793
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.
0
 
LVL 2

Expert Comment

by:macbone2
ID: 7020188
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(Cancel 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
0
 
LVL 12

Expert Comment

by:roverm
ID: 7020192
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
0
 
LVL 1

Author Comment

by:alanpong
ID: 7020609
RoverM,
yes, I want some toolbar buttons in my toolbar disable after all workbooks are closed.
0
 
LVL 12

Expert Comment

by:roverm
ID: 7021076
So, did you try my code yet ?
You will see the messagebox when all books are closed. ;-)

D'Mzzl!
RoverM
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 1

Author Comment

by:alanpong
ID: 7031930
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.
0
 
LVL 44

Expert Comment

by:bruintje
ID: 7032030
thanks, also to the others for the good contributions to the thread
0
 
LVL 12

Expert Comment

by:roverm
ID: 7032101
Thanks for the explanation !
Brian, you lucky b**** ;-))

D'Mzzl!
RoverM
0
 
LVL 12

Expert Comment

by:roverm
ID: 7032104
btw: Don't worry about not choosing my answer... I loved researching this and now I even used it myself. ;-)
0
 
LVL 44

Expert Comment

by:bruintje
ID: 7032136
well Mark it's friday afternoon so time to share the fun

http://www.experts-exchange.com/jsp/qManageQuestion.jsp?ta=msoffice&qid=20304323
0
 
LVL 12

Expert Comment

by:roverm
ID: 7032722
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.
0
 
LVL 44

Expert Comment

by:bruintje
ID: 7032743
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
0
 
LVL 12

Expert Comment

by:roverm
ID: 7032754
Looking forward to it! Should be easy ... ;-)))

D'Mzzl!
Mark
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
excel forecast function 1 30
Excel 2016 - What is this arrow pointing to a cell? 9 68
Excel 2016 - AutoFilter on selected columns issue 5 47
Oart.dll 2 43
Introduction Perhaps more familiar to developers who primarily use VBScript than to developers who tend to work only with Microsoft Office and Visual Basic for Applications (VBA), the Dictionary is a powerful and versatile class, and is useful …
Entering a date in Microsoft Access can be tricky. A typo can cause month and day to be shuffled, entering the day only causes an error, as does entering, say, day 31 in June. This article shows how an inputmask supported by code can help the user a…
This video walks the viewer through the process of creating Hyperlinks for the web and other documents. Select the "Insert" tab: Click "Hyperlink":  Type "http://" followed by a web address to reference a website or navigate to a document to ref…
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…

929 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now