Alternate Menu Shortcut

S

Steven

When I right click my mouse I get a long menu list on a popup which shows
next to the the current active cell. There are too many items on this popup.
Is there a way a can make it so only the Copy and Paste show up on this list.

Thank you for your help.

Steven
 
R

Ron de Bruin

Yes this is possible

You can run this macro (not possible manual)

Sub Change_Cell_Menu()
Dim IDnum As Variant
Dim N As Integer
Dim Ctl As CommandBarControl

'Set Enabled to False for all the controls
For Each Ctl In CommandBars("Cell").Controls
On Error Resume Next
Ctl.Enabled = False
On Error GoTo 0
Next Ctl

'Now set Enabled to True for the controls you want
IDnum = Array("22", "19")
For N = LBound(IDnum) To UBound(IDnum)
On Error Resume Next
Application.CommandBars("Cell").FindControl(ID:=IDnum(N), _
Recursive:=True).Enabled = True
On Error GoTo 0
Next N
End Sub

Sub All_Cell_Menu_Controls_Enabled_True()
'Set Enabled to True for all the controls in the Cell menu
Dim Ctl As CommandBarControl
For Each Ctl In CommandBars("Cell").Controls
On Error Resume Next
Ctl.Enabled = True
On Error GoTo 0
Next Ctl
End Sub

See also
http://www.rondebruin.com/menuid.htm
 
S

Steven

Ron,

This is really nice. I also included a Ctl.Visible = False which caused no
items to appear in the menu. What do I do so I see only the Copy and Paste
commands in the menu.

Thank you,

Steven
 
R

Ron de Bruin

Use this then

Sub Change_Cell_Menu()
Dim IDnum As Variant
Dim N As Integer
Dim Ctl As CommandBarControl

'Set Enabled to False for all the controls
For Each Ctl In CommandBars("Cell").Controls
On Error Resume Next
Ctl.Visible = False
On Error GoTo 0
Next Ctl

'Now set Enabled to True for the controls you want
IDnum = Array("22", "19")
For N = LBound(IDnum) To UBound(IDnum)
On Error Resume Next
Application.CommandBars("Cell").FindControl(ID:=IDnum(N), _
Recursive:=True).Visible = True
On Error GoTo 0
Next N
End Sub

Sub All_Cell_Menu_Controls_Visible _True()
'Set Enabled to True for all the controls in the Cell menu
Dim Ctl As CommandBarControl
For Each Ctl In CommandBars("Cell").Controls
On Error Resume Next
Ctl.Visible = True
On Error GoTo 0
Next Ctl
End Sub
 
S

Steven

Ron,

I figured it out looking at your website. I dont understand it but it works
and it is very helpful.

Thanks a bunch.

Steven
 
Top