Add msocontrolbutton to commandbar problem

[Ricky S]

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

 

[Rob van Gelder]

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