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 |
Add msocontrolbutton to commandbar problem
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" wrote in message ... 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 |
All times are GMT +1. The time now is 12:54 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com