How can the right-click shortcut menu in Excel be edited?

[Guest]

When I right-click a cell I get the shortcut menu with cut , copy, paste etc.
I would like to be able to edit this menu and add commands that I frequently
use. Hhow is this done?

 

[Ron de Bruin]

Look for the ID numbers on this page
http://www.rondebruin.com/menuid.htm

This example will add the Paste Special button to the Cell menu after the Paste option.

Sub Add_Paste_Special_Button()
' This will add the Paste Special button to the cell menu
' after the Paste option
Dim Num As Long
Num = Application.CommandBars("Cell"). _
FindControl(ID:=755).Index
Application.CommandBars("cell").Controls. _
Add Type:=msoControlButton, ID:=370, before:=Num
End Sub

Sub Delete_Paste_Special_Button()
On Error Resume Next
Application.CommandBars("cell").FindControl(ID:=370).Delete
On Error GoTo 0
End Sub

 

[GregR]

Ron, how is thecode ammended to add more than one item and begin a new group
with the additions. TIA

Greg

 

[Gord Dibben]

Greg

Not Ron, but sample code. I add my items when the workbook opens.

Sub Workbook_Open()

With Application.CommandBars("Cell").Controls.Add(temporary:=True)
.BeginGroup = True
.Caption = "Clear Formats"
.OnAction = "MyMacros.xla" & "!ClearFormatting"
End With

End Sub

Note: if going this route you should delete the items in a
workbook_beforeclose or in the workbook_open othewrwise you will get multiple
instances.

Application.CommandBars("Cell").Controls("Clear Formats").Delete


Gord Dibben Excel MVP

 

[GregR]

Gord, what if I want to add more than one button? Do I include the code
within the With..........End With Statement or do I write another one? Also,
what is the difference between a Control ID and .OnAction language. How do I
position the control in a certain spot on the menu? TIA

Greg

 

[Gord Dibben]

I just add another set of code within the Workbook_Open Sub.

With Application.CommandBars("Cell").Controls.Add(temporary:=True)
.Caption = "Clear Formats"
.OnAction = "MyMacros.xla" & "!Change References"
End With

I have added two groups of three items each to the bottom of my r-click menu
in this manner.

I have had no success trying to add more than one item within the With/End
With lines, but VBA is not my strong suit.

It's more like my birthday suit, which is kinda wrinkly<g>

There is most likely a way. Others will jump in if you hang around.

I think Ron's code with the Control ID is easier to use for exact placement.

Let's watch this thread so's we both can learn.


Gord

 

[Rob van Gelder]

Hi.

Here is a modification of a reply I made earlier today...

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

With Application.CommandBars("Cell")
For Each ctl In .Controls
If ctl.Tag = cTag Then ctl.Delete
Next

With .Controls.Add(Type:=msoControlButton, Before:=5,
Temporary:=True)
.Caption = "My Cell Item 1"
.Tag = cTag
.BeginGroup = True
.OnAction = ThisWorkbook.Name & "!my_macro"
.Parameter = "My Macro 1"
End With
With .Controls.Add(Type:=msoControlButton, Before:=6,
Temporary:=True)
.Caption = "My Cell Item 2"
.Tag = cTag
.OnAction = ThisWorkbook.Name & "!my_macro"
.Parameter = "My Macro 2"
End With
End With
End Sub

Public Sub my_macro()
With Application.CommandBars.ActionControl
MsgBox .Parameter
End With
End Sub


Note the Before and Temporary parameters.

 

[Ron de Bruin]

Another one if you only want to add Build-in controls that you use much.

Sub Add_Cell_Menu_Items()
Dim IDnum As Variant
Dim N As Integer
IDnum = Array("3", "4")
Application.CommandBars("Cell").Controls(1).BeginGroup = True
For N = LBound(IDnum) To UBound(IDnum)
On Error Resume Next
Application.CommandBars("Cell").Controls.Add _
Type:=msoControlButton, ID:=IDnum(N), before:=1
On Error GoTo 0
Next N
End Sub

Sub Delete_Cell_Menu_Items()
Dim IDnum As Variant
Dim N As Integer
IDnum = Array("3", "4")
For N = LBound(IDnum) To UBound(IDnum)
On Error Resume Next
Application.CommandBars("Cell").FindControl(ID:=IDnum(N)).Delete
On Error GoTo 0
Next N
End Sub

 

[Ron de Bruin]

Maybe use this

fill in the caption and macro name in the array
onaction_names = Array("macro1", "macro2", "macro3")
caption_names = Array("caption 1", "caption 2", "caption 3")


Sub Add_Controls()
Dim i As Long
Dim onaction_names As Variant
Dim caption_names As Variant

Delete_Controls

onaction_names = Array("macro1", "macro2", "macro3")
caption_names = Array("caption 1", "caption 2", "caption 3")

With Application.CommandBars("Cell")

For i = LBound(onaction_names) To UBound(onaction_names)
With .Controls.Add(Type:=msoControlButton)
.OnAction = ThisWorkbook.Name & "!" & onaction_names(i)
.Caption = caption_names(i)
End With
Next i

End With
End Sub

Sub Delete_Controls()
Dim i As Long
Dim caption_names As Variant

caption_names = Array("caption 1", "caption 2", "caption 3")

With Application.CommandBars("Cell")
For i = LBound(caption_names) To UBound(caption_names)
On Error Resume Next
.Controls(caption_names(i)).Delete
On Error GoTo 0
Next i
End With
End Sub

 

[GregR]

Ron, Rob and Gord, thank you very much.............

Greg

Maybe use this

fill in the caption and macro name in the array
onaction_names = Array("macro1", "macro2", "macro3")
caption_names = Array("caption 1", "caption 2", "caption 3")


Sub Add_Controls()
Dim i As Long
Dim onaction_names As Variant
Dim caption_names As Variant

Delete_Controls

onaction_names = Array("macro1", "macro2", "macro3")
caption_names = Array("caption 1", "caption 2", "caption 3")

With Application.CommandBars("Cell")

For i = LBound(onaction_names) To UBound(onaction_names)
With .Controls.Add(Type:=msoControlButton)
.OnAction = ThisWorkbook.Name & "!" & onaction_names(i)
.Caption = caption_names(i)
End With
Next i

End With
End Sub

Sub Delete_Controls()
Dim i As Long
Dim caption_names As Variant

caption_names = Array("caption 1", "caption 2", "caption 3")

With Application.CommandBars("Cell")
For i = LBound(caption_names) To UBound(caption_names)
On Error Resume Next
.Controls(caption_names(i)).Delete
On Error GoTo 0
Next i
End With
End Sub


--
Regards Ron de Bruin
http://www.rondebruin.nl



"Gord Dibben" <gorddibbATshawDOTca> wrote in message news:[email protected]...

I just add another set of code within the Workbook_Open Sub.

With Application.CommandBars("Cell").Controls.Add(temporary:=True)
.Caption = "Clear Formats"
.OnAction = "MyMacros.xla" & "!Change References"
End With

I have added two groups of three items each to the bottom of my r-click menu
in this manner.

I have had no success trying to add more than one item within the With/End
With lines, but VBA is not my strong suit.

It's more like my birthday suit, which is kinda wrinkly<g>

There is most likely a way. Others will jump in if you hang around.

I think Ron's code with the Control ID is easier to use for exact placement.

Let's watch this thread so's we both can learn.


Gord

Gord, what if I want to add more than one button? Do I include the code
within the With..........End With Statement or do I write another one? Also,
what is the difference between a Control ID and .OnAction language. How do I
position the control in a certain spot on the menu? TIA

Greg
"Gord Dibben" <gorddibbATshawDOTca> wrote in message
Greg

Not Ron, but sample code. I add my items when the workbook opens.

Sub Workbook_Open()

With Application.CommandBars("Cell").Controls.Add(temporary:=True)
.BeginGroup = True
.Caption = "Clear Formats"
.OnAction = "MyMacros.xla" & "!ClearFormatting"
End With

End Sub

Note: if going this route you should delete the items in a
workbook_beforeclose or in the workbook_open othewrwise you will get
multiple
instances.

Application.CommandBars("Cell").Controls("Clear Formats").Delete


Gord Dibben Excel MVP

 

[Gord Dibben]

And I did learn something by watching<g>


Gord

Ron, Rob and Gord, thank you very much.............

Greg

Maybe use this

fill in the caption and macro name in the array
onaction_names = Array("macro1", "macro2", "macro3")
caption_names = Array("caption 1", "caption 2", "caption 3")


Sub Add_Controls()
Dim i As Long
Dim onaction_names As Variant
Dim caption_names As Variant

Delete_Controls

onaction_names = Array("macro1", "macro2", "macro3")
caption_names = Array("caption 1", "caption 2", "caption 3")

With Application.CommandBars("Cell")

For i = LBound(onaction_names) To UBound(onaction_names)
With .Controls.Add(Type:=msoControlButton)
.OnAction = ThisWorkbook.Name & "!" & onaction_names(i)
.Caption = caption_names(i)
End With
Next i

End With
End Sub

Sub Delete_Controls()
Dim i As Long
Dim caption_names As Variant

caption_names = Array("caption 1", "caption 2", "caption 3")

With Application.CommandBars("Cell")
For i = LBound(caption_names) To UBound(caption_names)
On Error Resume Next
.Controls(caption_names(i)).Delete
On Error GoTo 0
Next i
End With
End Sub


--
Regards Ron de Bruin
http://www.rondebruin.nl



"Gord Dibben" <gorddibbATshawDOTca> wrote in message news:[email protected]...

I just add another set of code within the Workbook_Open Sub.

With Application.CommandBars("Cell").Controls.Add(temporary:=True)
.Caption = "Clear Formats"
.OnAction = "MyMacros.xla" & "!Change References"
End With

I have added two groups of three items each to the bottom of my r-click menu
in this manner.

I have had no success trying to add more than one item within the With/End
With lines, but VBA is not my strong suit.

It's more like my birthday suit, which is kinda wrinkly<g>

There is most likely a way. Others will jump in if you hang around.

I think Ron's code with the Control ID is easier to use for exact placement.

Let's watch this thread so's we both can learn.


Gord