Add msocontrolbutton to commandbar problem

I'm trying to add a controlbutton to the popup menu when you right click a
worksheet.
I've got the following sub working when I run it from one worksheet but when
I put it into another one (a template) it doesn't work and I get an "Invalid
procedure call or argument" error. When I debug it in the first sheet the
value for msoControlButton = 1, but when in the template it is empty.

Sub add_menu_item()
delete_menu_item
With Application.CommandBars("Cell").Controls
With .Add(msoControlButton)
.Caption = "My Item"
.OnAction = "my_macro"
End With
End With
End Sub

Private Sub my_macro()
MsgBox ("Hello")
End Sub

Do you see the item on the context menu when you right-click?

If it's having trouble with msoControlButton then check the Reference:
Tools | References | Microsoft Office 11.0 Object Library
(your version might differ)


Here is the code I might use:

Sub test()
Const cCaption = "My Item"
Dim ctl As CommandBarControl

With Application.CommandBars("Cell")
'Delete existing
For Each ctl In .Controls
If ctl.Caption = cCaption Then
ctl.Delete
Exit For
End If
Next

With .Controls.Add(Type:=msoControlButton)
.Caption = cCaption
.OnAction = ThisWorkbook.Name & "!my_macro"
End With
End With

End Sub

Public Sub my_macro()
MsgBox ("Hello")
End Sub



--
Rob van Gelder - http://www.vangelder.co.nz/excel


"Ricky S" <why767@hotmail.com> wrote in message
news:uMKGiEYBFHA.3320@TK2MSFTNGP10.phx.gbl...
> I'm trying to add a controlbutton to the popup menu when you right click a
> worksheet.
> I've got the following sub working when I run it from one worksheet but
> when
> I put it into another one (a template) it doesn't work and I get an
> "Invalid
> procedure call or argument" error. When I debug it in the first sheet the
> value for msoControlButton = 1, but when in the template it is empty.
>
> Sub add_menu_item()
> delete_menu_item
> With Application.CommandBars("Cell").Controls
> With .Add(msoControlButton)
> .Caption = "My Item"
> .OnAction = "my_macro"
> End With
> End With
> End Sub
>
> Private Sub my_macro()
> MsgBox ("Hello")
> End Sub
>
>