How To avoid the SAving of toolbars while saving

Hari_Word · July 15th, 2004, 07:40 AM

I have created a custom tool bar for a active work book in excel.
I have to save the excel file but the toolbar created must not be saved.But the toolbar should be present till the workbook is closed.

How to achieve this?

Hari

slgknjn · July 15th, 2004, 11:48 AM

Hi
As I haven't seen your code, I do not know how you created it. But to let it be visible until you close the workbook, you should delete it in the.
Workbook_BeforeClose event. So in the module 'ThisWorkbook'
you do something like this.

eg.
Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Call RemoveCustomMenu
End Sub

And in a normal module place something like this.

Public Sub RemoveCustomMenu()
   Dim cbWSMenuBar As CommandBar

   On Error Resume Next
   Set cbWSMenuBar = CommandBars("Worksheet Menu Bar")
   cbWSMenuBar.Controls("MyMenu").Delete
End Sub

Hope it helped.
Cheers
Karsten

Hari_Word · July 16th, 2004, 03:48 AM

But this will promt me to save the workbook as the toolbar is deleted.

chrislepingwell · July 16th, 2004, 05:36 AM

If you don't want to save anything in the workbook add the line

Code:

thisworkbook.saved True

to the Workbook_BeforeClose event. This tricks Excel into believing that there have been no changes since the last save.

Chris

There are two secrets to success in this world:
1. Never tell everything you know

gcianfanelli · July 19th, 2004, 03:46 AM

You could also try:

Code:

Sub createMyToolbar()
  With Application.CommandBars.Add(Name:="My Temporary Toolbar", Temporary:=True)
    With .Controls.Add(Type:=msoControlButton, Temporary:=True)
      .Caption = "My Temporary Button"
      .OnAction = "MyOnActionProperty"
    End With
  End With
End Sub

In other words, create the toolbar/button(s) using the Temporary parameter.

You could place this code in a global XLA (Excel Addin) file to be copied into your XLSTART folder.

You'd probably then want to launch this code from something like an AutoExec procedure within the global addin.

slgknjn · July 19th, 2004, 11:08 PM

Hi
If you want to save without being promted, you can put the following in the
Workbook_BeforeClose event.

If Not ThisWorkbook.Saved Then
ThisWorkbook.Save
End If

Cheers
Karsten

Hari_Word · July 26th, 2004, 12:13 AM

thanks a lot