Crerating a temporary menu button
 

If you use temporary in your code the control will be deleted only if you close Excel

Here is a example

Run the macro in this events in the Thisworkbook module for example

Private Sub Workbook_Activate()
MenuBar_Item
End Sub

Private Sub Workbook_Deactivate()
MenuBar_Item_Delete
End Sub

This code must be in a normal module

Sub MenuBar_Item()
On Error Resume Next
Application.CommandBars(1).Controls("Hi").Delete
On Error GoTo 0

With Application.CommandBars(1)
.Controls.Add(Type:=msoControlPopup, befo=1).Caption = "&Hi"
.Controls("Hi").OnAction = ThisWorkbook.Name & "!TestMacro"
End With
End Sub

Sub MenuBar_Item_Delete()
On Error Resume Next
Application.CommandBars(1).Controls("Hi").Delete
On Error GoTo 0
End Sub

Sub TestMacro()
MsgBox "Hi"
End Sub


--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2002 SP-2)
www.rondebruin.nl



"Lionel Fridjhon" wrote in message ...
am trying to create a menu button that is deleted when
the workbook is closed.

I have the following piece of code that puts the button
in place, but it remains in the worksheet toolbar when I
close:

Set ComBar = CommandBars("worksheet menu bar")
With ComBar
.Controls.Add(Type:=msoControlButton,
temporary:=True, befo=6).Caption = "&Process CLiMET"
.Controls("Process CLiMET").Style =
msoButtonCaption
.Controls("PROCESS CLIMET").OnAction
= "SelectFolderToProcess"
End With

Can anyone help?

Lionel