Deleting new control button on auto_close??

[Simon Lloyd]

I have some code which creates a menu bar item to disable/enable events,
how do i delete this item on Auto_close?
Do i say For Each c In .Captions
If c.Caption = "EN" Then Delete
Next

Would that do it?

Simon.

Heres the code i use to create the menu item......

Dim c As Variant
On Error Resume Next
With Application.CommandBars("Worksheet Menu Bar")
For Each c In .Controls
If c.Caption = "EN" Then c.Delete
Next c
..Controls.Add Type:=msoControlButton, ID:=2950, Before:=1
..Controls(1).Caption = "EN"
..Controls(1).TooltipText = "Enable Events"
..Controls(1).OnAction = ThisWorkbook.Name & "!enevents"
..Controls(1).Style = msoButtonCaption
End With
End Sub
--------------------



Sub enevents()
Application.EnableEvents = Not Application.EnableEvents
End Sub

 

[JE McGimpsey]

One way:

With Application.CommandBars("Worksheet Menu Bar")
On Error Resume Next
.Controls("EN").Delete
On Error GoTo 0
With .Controls.Add(Type:=msoControlButton, _
Id:=2950, Before:=1)
.Caption = "EN"
.TooltipText = "EnableEvents"
.OnAction = ThisWorkbook.Name & "!enevents"
.Style = msoButtonCaption
End With
End With