Results 1 to 4 of 4
  1. #1
    Lounger
    Join Date
    Feb 2004
    Location
    Hatboro, Pennsylvania, USA
    Posts
    28
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Restore Toolbar if User cancels Close File (2000)

    I have a Sub that removes a temporary custom toolbar when the workbook is closed, which is fine unless the User elects to 'Cancel' out of the Excel dialog "Do you want to save changes... ?".

    In that case, the file remains open (which is what the User intends), but the toolbar is now removed anyway. <img src=/S/disappointed.gif border=0 alt=disappointed width=15 height=15>

    Is there some way to have the custom toolbar recovered (or not removed) if 'Cancel' is selected in that dialog ??


    Private Sub Workbook_Open()
    Call AddMyCustomToolbar
    End Sub


    Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Call DeleteCustomToolbar("myToolbar")
    End Sub

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

    Re: Restore Toolbar if User cancels Close File (2000)

    If the toolbar is only relevant to the particular workbook, then the Workbook Activate and Workbook Deactivate event routines are much better places to build and remove the toolbar.
    Legare Coleman

  3. #3
    Lounger
    Join Date
    Feb 2004
    Location
    Hatboro, Pennsylvania, USA
    Posts
    28
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Restore Toolbar if User cancels Close File (2000)

    I do also have a call to the Toolbar creation Sub in the Workbook_Activate sub, but with only this workbook open in the first place, that doesn't solve the problem.

    My work-around:
    Fooling around with this problem a little more, I wrote this little Sub, and stuck a call to it in the Workbook_SheetActivate sub:

    Sub CheckForToolbar()
    'Adds custom toolbar if absent
    On Error GoTo RestoreToolbar
    With Application.CommandBars("myToolbar")
    End With
    Exit Sub
    RestoreToolbar:
    Call AddMyCustomToolbar
    End Sub

    Now the User gets the toolbar back by simply changing the worksheet selection.

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

    Re: Restore Toolbar if User cancels Close File (2000)

    Calling code only from the Workbook_Activate and Workbook_Deactivate events, not from Workbook_BeforeClose, should take care of everything.

Posting Permissions

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