Results 1 to 11 of 11
  1. #1
    3 Star Lounger
    Join Date
    Mar 2001
    Location
    Silicon Valley, California, USA
    Posts
    273
    Thanks
    0
    Thanked 0 Times in 0 Posts

    BeforeClose and Cancel (Excel 2000, SR1)

    Hi Gang,

    I have a template that generates its own toolbar on the fly, and cleans up when the workbook closes. To do so, I have a CommandBars("SuchAndSuch").Delete in the Workbook_BeforeClose event.

    However, if the user accidentally closes the workbook and gets prompted about saving before they quit, they can cancel the accidental Close. By then, the toolbar is gone. What event can I monitor to re-generate the toolbar in such a situation?

    Thanks!

  2. #2
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Dallas plus 20 miles or so, Texas, USA
    Posts
    876
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: BeforeClose and Cancel (Excel 2000, SR1)

    Hi Jim,

    I have a similar instance and I have not gotten around to solving it yet, but I am thinking that if you use
    <pre>If Cancel = True then <font color=red>call create toolbar sub<font color=red>
    </pre>


    It will recreate the toolbar if the user decides not to close the book. There may be some other way out there, but off the cuff, this is all I can think of.

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

    Re: BeforeClose and Cancel (Excel 2000, SR1)

    Here is one way to solve that.

    In your before close event, enter this line:

    Application.Ontime Now, "ReInstate"

    ReInstate is a Sub in a normal module that re-initializes your application completely.

    What happens is, your workbook closes normally, but an event is set to run a macro from your workbook, which forces it to reload (unfortunately also triggering the macro warning again). If however the closure is caused by a termination of Excel, nothing happens after the closure of your workbook, XL terminates normally.
    It is a tricky routine however, because after firing your ReInstate routine, the workbook_open event will also fire. You'll have to set a global boolean variable in ReInstate so the workbook Open event knows something already has been done.

    To avoid the macro warning message, save your workbook as an add-in and treat it as such by installing it from Tools, Add-ins..
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  4. #4
    2 Star Lounger
    Join Date
    Feb 2001
    Location
    Chicago, Illinois, USA
    Posts
    185
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: BeforeClose and Cancel (Excel 2000, SR1)

    Hi JIM--I am trying to do exactly what you say you've done, and I can't do it (see the thread I started today)...would you mind posting your code or attaching your workbook? I am going nuts....

    Thanks.

    klyjen

  5. #5
    3 Star Lounger
    Join Date
    Mar 2001
    Location
    Silicon Valley, California, USA
    Posts
    273
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: BeforeClose and Cancel (Excel 2000, SR1)

    Here is the workbook. I don't think I have implemented either idea presented in this thread, and I don't have time now to look. But here it is, for your reading pleasure.
    Attached Files Attached Files

  6. #6
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: BeforeClose and Cancel (Excel 2000, SR1)

    I solved that problem by using the workbook Activate and Deactivate events to do my menu customizations.
    Legare Coleman

  7. #7
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Kortrijk, Belgium
    Posts
    571
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: BeforeClose and Cancel (Excel 2000, SR1)

    As Legare said, the way to do it is using the deactivate event:

    Declare the variable CloseWB as boolean in a general module.

    Dim CloseWB as Boolean

    <pre>Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Cancel = False
    CloseWB = Not Cancel
    End Sub
    </pre>


    <pre>Private Sub Workbook_Deactivate()
    On Error Resume Next
    If CloseWB = True Then
    Application.CommandBars("MyToolbar").Delete
    Else
    Application.CommandBars("MyToolbar").Visible = False
    End If
    On Error GoTo 0
    End Sub
    </pre>


    When one presses Cancel when he/she is prompted with the Excel message about saving, then the toolbar is not deleted. When choosing for closing, the workbook will be deactivated, which calls the deactivate event, where the toolbar is deleted. If you are simply leaving your workbook by activating another one, the toolbar will only be hidden. Of course, you must unhide it when the workbook is activated again, using the code below:

    <pre>Private Sub Workbook_Activate()
    On Error Resume Next
    Application.CommandBars("MyToolbar").Visible = True
    On Error GoTo 0
    End Sub
    </pre>


  8. #8
    3 Star Lounger
    Join Date
    Mar 2001
    Location
    Silicon Valley, California, USA
    Posts
    273
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: BeforeClose and Cancel (Excel 2000, SR1)

    Thanks Legare and Hans,

    I fixed my sample by simply eliminating the code for Workbook_BeforeClose event. If the user closes, then presses cancel when prompted for saving changes, the Workbook_Deactivate is not executed and the toolbar remains intact. If they choose Yes or No, the Workbook_Deactivate is executed and the toolbar is removed.

  9. #9
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: BeforeClose and Cancel (Excel 2000, SR1)

    If you didn't, you should probably also move your code to display your toolbar/menu to the Activate event. If you didn't do that, then if two workbooks are open, you will lose your toolbar/menu if you switch to it and won't get it back when you switch back.
    Legare Coleman

  10. #10
    3 Star Lounger
    Join Date
    Mar 2001
    Location
    Silicon Valley, California, USA
    Posts
    273
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: BeforeClose and Cancel (Excel 2000, SR1)

    My workbook already has the necessary code in the Workbook_Activate and Workbook_Deactivate to cover these situations. The problem was, the code in BeforeClose was not necessary.

    Thanks!

  11. #11
    2 Star Lounger
    Join Date
    Feb 2001
    Location
    Chicago, Illinois, USA
    Posts
    185
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: BeforeClose and Cancel (Excel 2000, SR1)

    Thanks Jim! As it turns out, the problem I was having was with our DMS. Grr. But your code will be a good reference, so I appreciate it...

    ~jen

Posting Permissions

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