Delete Toolbar on close of Excel 1

[rjm65]

How can I delete a custom toolbar that I have created on the close of Excel? I have a macro that saves as , closes the saved workbook, and opens the original workbook. I need to delete the toolbar when Excel is closed so that it only appears when my user is working with the file I created for him.

Thanks,
Raymond

 

[TonyJollans]

Hi Raymond,

If you create your toolbar with [blue]Temporary:=True[/blue] it should be deleted automatically. However I believe that doesn't work correctly in all versions of Excel.

If you want to remove it when your Workbook is closed (which seems to be what you want) then you need to put code in the [blue]Workbook_BeforeClose[/blue] event. In the [blue]ThisWorkbook[/blue] code module select [blue]Workbook[/blue] from the dropdown at the top left and [blue]BeforeClose[/blue] from the dropdown at the right. This will give you an empty procedure; inside it, code ..

[blue][tt] Application.Commandbars("YourToolbar").Delete[/tt][/blue]

If you want to remove the toolbar only when the Excel application is terminated you will need to use Application Events. Please post back if that is what you want.

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at [url=http://www.vbaexpress.

 

[rjm65]

Hi Tony,
I have things currently setup as you described, using Workbook_BeforeClose to delete my custom toolbar. The problem is this, one of the buttons on the custom toolbar runs a macro that saves the current file, and then opens up the original blank file.

Here's what happens as the workbook is saved:

ActiveWorkbook.SaveAs fol & "\" & SuggName & ".xls"
MsgBox " PO for " & venname & " has been saved! !", vbInformation, "Saved Purchase Order Information"
Dim Wb As Workbook
Set Wb = ActiveWorkbook
Workbooks.Open Filename:="C:\QCPO2.xls" 'my blank workbook
Wb.Close
Windows("QCPO2.xls").Activate

Deleting the toolbar in the Workbook_close deletes the toolbar when Wb.Close command runs, and I am not able to use it with my QCPO.xls file that opens. I have a feeling I just don't have things in the proper sequence. I have tried to place Wb.Close before opening my blank workbook QCPO2.XLS, but then the blank workbook never opens.

Thanks,
Raymond

 

[TonyJollans]

Hi Raymond,

Yes, I should have read the original question a bit more closely! You could get into some complex logic checking if the toolbar is, or should be, displayed and I'm not sure I know enough about your application to advise on what that logic should be.

As an alternative, what about using the SaveCopyAs method and then reopening the (still open) original. You will be prompted to discard changes but the message can be suppressed, like this:

[blue]ActiveWorkbook.Save[red]Copy[/red]As fol & "\" & SuggName & ".xls"
MsgBox " PO for " & venname & " has been saved! !", vbInformation, "Saved Purchase Order Information"
Application.DisplayAlerts = False
Workbooks.Open Filename:="C:\QCPO2.xls"  'my blank workbook[/blue]

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at [url=http://www.vbaexpress.

 

[rjm65]

Thanks Tony!! Changing SaveAs to SaveCopyAs made everything work exactly like I wanted it to.

I don't actually close the active workbook now after the save process, I keep the original workbook open and just clear the input fields to start the process all over again.

Now I just have a minor inconvenience. If I open and close my file, there is no confirmation message on close (as it should be). If I open my file, run my code using SaveCopyAs, then I am prompted on exit of my original file to "save, discard changes." I don't understand why the "before close" code listed below doesn't cure that. Any ideas?

Raymond

Private Sub Workbook_BeforeClose(Cancel As Boolean)
On Error Resume Next
Application.CommandBars("PO Forms").Delete
Application.DisplayAlerts = False
End Sub

 

[TonyJollans]

Hi Raymond,

Unfortunately the prompt is issued before the close (and even before the save) is attempted and therefore is not affected by code in the BeforeClose (or BeforeSave) events.

It might help if you added ..
[blue][tt] ActiveWorkbook.Saved = True[/tt][/blue]
.. at the end of the routine where you re-initialise the workbook. The prompt won't then be issued until further changes are made.

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at [url=http://www.vbaexpress.