Results 1 to 7 of 7
  1. #1
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Yilgarn region of Toronto, Ontario
    Posts
    5,453
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Excel FileCloseAll (Excel XP )

    Is there a way to trap/distinguish the event that fires up after holding the Shift key, choosing CloseAll from the File menu?

    I've tried a macro "Public Sub FileCloseAll" with no success.
    The "Workbook_BeforeClose" event doesn't have a flag such as the "Workbook_BeforeSave" event's "SaveAsUI As Boolean".
    I have a "Workbook_BeforeClose" but see no "Workbook_BeforeCloseAll" event.

    In general I am finding that ExcelXP differs from WordXP in the sense that in Excel I can not rely on a suite of stand-in macros, such as FileSave, FileClose, FileSaveAs etc. as I am used to doing in Word.

    That is, I've got a nice set of macros in WordXP that trap all sorts of save/close events from the end-user, and pass parameters to my developer code.
    ExcelXP seems not to provide these handy "hooks".

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Excel FileCloseAll (Excel XP )

    As far as I know, it's not possible to detect that the user has selected File | Close All.

  3. #3
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Yilgarn region of Toronto, Ontario
    Posts
    5,453
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel FileCloseAll (Excel XP )

    Thanks, Hans.

    I searched both the Lounge and the Google Groups with no success. I'm amazed that there haven't been previous postings.

    I suspect that in time I'll find the Excel solution more elegant, and the Word solution child-like, but it is now such a customary disappointment to find these out-of-phase design features between MSOffice products.

    If the Excel solutions are more modern/elegant, it is a pity that the old-fashioned Word approach wasn't brought across for backward compatibility.

  4. #4
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Re: Excel FileCloseAll (Excel XP )

    The event should fire for each workbook. I'm not sure why you need to know that the user is closing all of them. ???

  5. #5
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Yilgarn region of Toronto, Ontario
    Posts
    5,453
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel FileCloseAll (Excel XP )

    > I'm not sure why you need to know that the user is closing all of them.

    The main reason is that I trapped FileCloseAll in WordXP, and wanted to port that code directly over to Excel, so that with 60 minute's work I could tell the client "Look! Everything we worked on in Word now also works in Excel!!".

    Now that may be historic, and it may be that Word is old-fashioned compared to Excel, and that because I could trap FileCloseAll in Word, I wrote a macro for it, and embedded code<pre> For Each doc In Documents
    Call CloseOneDocument(doc)
    Next doc
    </pre>

    I may have been leading myself down a false path in implementing FileCloseAll in Word. But that's where I went, and that's why I felt an urge to go there in Excel.

    It may be time to revise my ideas and write this "properly" in both Word and Excel, but my puzzlement was that Excel didn't visit a FileCloseAll macro; I didn't know that Excel wouldn't, and was thinking that maybe once again I'd managed to de-standardize my Office installation!

  6. #6
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel FileCloseAll (Excel XP )

    You could fake it by detecting whether the shift key is held down.

    This detects the shift key: (normal module)

    <div style="width: 100%; background-color: #FFFFFF;"><code><font color=black><font color=blue>Option</font color=blue> <font color=blue>Explicit</font color=blue>

    <font color=448800>'Declare API</font color=448800>
    Declare <font color=blue>Function</font color=blue> GetKeyState <font color=blue>Lib</font color=blue> "User32" _
    (<font color=blue>ByVal</font color=blue> vKey <font color=blue>As</font color=blue> <font color=blue>Integer</font color=blue>) <font color=blue>As</font color=blue> Integer

    <font color=blue>Const</font color=blue> SHIFT_KEY = 16

    <font color=blue>Public</font color=blue> bAll <font color=blue>As</font color=blue> <font color=blue>Boolean</font color=blue>
    <font color=blue>Dim</font color=blue> AppClass <font color=blue>As</font color=blue> <font color=blue>New</font color=blue> EventClass
    <font color=blue>Public</font color=blue> gclsControlEvents <font color=blue>As</font color=blue> CControlEvents

    <font color=blue>Function</font color=blue> ShiftPressed() <font color=blue>As</font color=blue> <font color=blue>Boolean</font color=blue>
    <font color=448800>'Returns True if shift key is pressed</font color=448800>
    ShiftPressed = GetKeyState(SHIFT_KEY) < 0
    <font color=blue>End</font color=blue> <font color=blue>Function</font color=blue></font color=black></code></div hiblock>

    Then use something like this in the application event class module's before close event:

    <div style="width: 100%; background-color: #FFFFFF;"><code><font color=black><font color=blue>Option</font color=blue> <font color=blue>Explicit</font color=blue>

    <font color=blue>Public</font color=blue> <font color=blue>WithEvents</font color=blue> App <font color=blue>As</font color=blue> Application


    <font color=blue>Private</font color=blue> <font color=blue>Sub</font color=blue> App_WorkbookBeforeClose(<font color=blue>ByVal</font color=blue> Wb <font color=blue>As</font color=blue> Workbook, Cancel As <font color=blue>Boolean</font color=blue>)
    <font color=blue>If</font color=blue> ShiftPressed <font color=blue>Then</font color=blue>
    MsgBox "closing all"
    <font color=blue>Else</font color=blue>
    MsgBox "closing one"
    <font color=blue>End</font color=blue> <font color=blue>If</font color=blue>
    End <font color=blue>Sub</font color=blue>
    </font color=black></code></div hiblock>
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  7. #7
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Yilgarn region of Toronto, Ontario
    Posts
    5,453
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel FileCloseAll (Excel XP )

    Thanks Jan. That works just fine (and is a handy-dandy little chunk of utility code to boot).

    I'm now in a state of flux, having abandoned my incarnation of a set of FileSave, FileSaveAs, FileClose etc. macros for the two application events app_WorkbookBeforeClose and app_WorkbookBeforeSave, with their parallels in MS Word.

    All seems to be working fine; I don't have to agonize over the BigX Little X discrimination either, since I'm now focused solely on a single document, regardless of where the call originated.

    Sadly, I seem to have a bug in WordXP that crashes Word when (1) Autosave is in effect and (2) two separate applications try to trap the Save events.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •