Using VBA to change the contextual menus

[Mike Magill]

Hi,

I've cobbled together the following two scripts to change how the
contextual menus look but I get a 'Run Time Error '5': Invalid
procedure call or arguement'. When I debug it points to the line:

Application.CommandBars("Cell").Controls("Insert...").Delete

If I comment this line out the script works fine but I can't work out
what's wrong with this line.

Any help would be greatly appreciated.

Many thanks,



Private Sub Workbook_Activate()
' This macro changes the contextual menus for Cells, Rows and Columns
to limit
' and control the user's ability to interact with the spreadsheet.

Application.CommandBars("Column").Enabled = False

Application.CommandBars("Cell").Reset

Application.CommandBars("Cell").Controls("Cut").Delete
Application.CommandBars("Cell").Controls("Insert...").Delete
Application.CommandBars("Cell").Controls("Delete...").Delete
Application.CommandBars("Cell").Controls("Format Cells...").Delete
Application.CommandBars("Cell").Controls("Pick From Drop-down
List...").Delete
Application.CommandBars("Cell").Controls("Add Watch").Delete
Application.CommandBars("Cell").Controls("Create List...").Delete
Application.CommandBars("Cell").Controls("Hyperlink...").Delete
Application.CommandBars("Cell").Controls("Look Up...").Delete

Application.CommandBars("Row").Reset

Application.CommandBars("Row").Controls("Cut").Delete
Application.CommandBars("Row").Controls("Copy").Delete
Application.CommandBars("Row").Controls("Paste").Delete
Application.CommandBars("Row").Controls("Paste Special...").Delete
Application.CommandBars("Row").Controls("Insert...").Delete
Application.CommandBars("Row").Controls("Delete...").Delete
Application.CommandBars("Row").Controls("Clear Contents").Delete
Application.CommandBars("Row").Controls("Format Cells...").Delete
Application.CommandBars("Row").Controls("Row Height...").Delete
Application.CommandBars("Row").Controls("Hide").Delete
Application.CommandBars("Row").Controls("Unhide").Delete

With Application.CommandBars("Row").Controls
With .Add
.Caption = "&Insert Row"
.OnAction = ThisWorkbook.Name & "!InsertRow"
.BeginGroup = True
End With

With .Add
.Caption = "&Delete Row"
.OnAction = ThisWorkbook.Name & "!DeleteRow"
End With
End With

End Sub

Private Sub Workbook_Deactivate()
' This macro reinstates the contextual menus to their standard state

Application.CommandBars("Cell").Reset
Application.CommandBars("Row").Reset
Application.CommandBars("Column").Enabled = True
Application.CommandBars("Column").Reset

End Sub

 

[Ron de Bruin]

Hi Mike

It looks good if you use a English version
are you sure Insert... is there before you run the code

Always use findcontrol with Id's if you do this, see :
http://www.rondebruin.nl/menuid.htm

 

[Don Guillett]

Ron, How do we do CUSTOM menus in xl2007??

 

[Ron de Bruin]

Hi Don

The right click menu's are the same as in older versions (with VBA)
If you want to change the Ribbon you must add RibbonX to the workbook
http://www.rondebruin.nl/ribbon.htm

For a easy menu in the QAT, you can also use
http://www.rondebruin.nl/qat.htm

Or do you want to know something else ?