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?

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, befo=Num
End Sub

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


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



"LynneN" wrote in message ...
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, how is thecode ammended to add more than one item and begin a new group
with the additions. TIA

Greg
"Ron de Bruin" wrote in message
...
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, befo=Num
End Sub

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


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



"LynneN" wrote in message
...
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?

Greg

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

Sub Workbook_Open()

With Application.CommandBars("Cell").Controls.Add(tempo rary:=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

On Fri, 28 Jan 2005 11:09:10 -0800, "GregR" wrote:

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

Greg
"Ron de Bruin" wrote in message
...
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, befo=Num
End Sub

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


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



"LynneN" wrote in message
...
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?

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(tempo rary:=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

On Fri, 28 Jan 2005 11:09:10 -0800, "GregR" wrote:

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

Greg
"Ron de Bruin" wrote in message
...
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, befo=Num
End Sub

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


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



"LynneN" wrote in message
...
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?

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

With Application.CommandBars("Cell").Controls.Add(tempo rary:=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

On Fri, 28 Jan 2005 16:25:56 -0800, "GregR" wrote:

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(tempo rary:=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

On Fri, 28 Jan 2005 11:09:10 -0800, "GregR" wrote:

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

Greg
"Ron de Bruin" wrote in message
...
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, befo=Num
End Sub

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


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



"LynneN" wrote in message
...
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?

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, Befo=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, Befo=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.



--
Rob van Gelder - http://www.vangelder.co.nz/excel


"Gord Dibben" <gorddibbATshawDOTca wrote in message
...
I just add another set of code within the Workbook_Open Sub.

With Application.CommandBars("Cell").Controls.Add(tempo rary:=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

On Fri, 28 Jan 2005 16:25:56 -0800, "GregR" wrote:

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(tempo rary:=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

On Fri, 28 Jan 2005 11:09:10 -0800, "GregR"
wrote:

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

Greg
"Ron de Bruin" wrote in message
...
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, befo=Num
End Sub

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


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



"LynneN" wrote in message
...
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?

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).BeginG roup = True
For N = LBound(IDnum) To UBound(IDnum)
On Error Resume Next
Application.CommandBars("Cell").Controls.Add _
Type:=msoControlButton, ID:=IDnum(N), befo=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:=ID num(N)).Delete
On Error GoTo 0
Next N
End Sub


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



"Rob van Gelder" wrote in message ...
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, Befo=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, Befo=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.



--
Rob van Gelder - http://www.vangelder.co.nz/excel


"Gord Dibben" <gorddibbATshawDOTca wrote in message ...
I just add another set of code within the Workbook_Open Sub.

With Application.CommandBars("Cell").Controls.Add(tempo rary:=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

On Fri, 28 Jan 2005 16:25:56 -0800, "GregR" wrote:

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(tempo rary:=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

On Fri, 28 Jan 2005 11:09:10 -0800, "GregR" wrote:

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

Greg
"Ron de Bruin" wrote in message
...
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, befo=Num
End Sub

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


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



"LynneN" wrote in message
...
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?

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 ...
I just add another set of code within the Workbook_Open Sub.

With Application.CommandBars("Cell").Controls.Add(tempo rary:=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

On Fri, 28 Jan 2005 16:25:56 -0800, "GregR" wrote:

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(tempo rary:=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

On Fri, 28 Jan 2005 11:09:10 -0800, "GregR" wrote:

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

Greg
"Ron de Bruin" wrote in message
...
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, befo=Num
End Sub

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


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



"LynneN" wrote in message
...
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, Rob and Gord, thank you very much.............

Greg
"Ron de Bruin" wrote in message
...
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
...
I just add another set of code within the Workbook_Open Sub.

With Application.CommandBars("Cell").Controls.Add(tempo rary:=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

On Fri, 28 Jan 2005 16:25:56 -0800, "GregR"
wrote:

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(tempo rary:=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

On Fri, 28 Jan 2005 11:09:10 -0800, "GregR"
wrote:

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

Greg
"Ron de Bruin" wrote in message
...
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, befo=Num
End Sub

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


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



"LynneN" wrote in message
...
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?

And I did learn something by watching<g


Gord

On Mon, 31 Jan 2005 14:38:11 -0800, "GregR" wrote:

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

Greg
"Ron de Bruin" wrote in message
...
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
.. .
I just add another set of code within the Workbook_Open Sub.

With Application.CommandBars("Cell").Controls.Add(tempo rary:=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

On Fri, 28 Jan 2005 16:25:56 -0800, "GregR"
wrote:

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(tempo rary:=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

On Fri, 28 Jan 2005 11:09:10 -0800, "GregR"
wrote:

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

Greg
"Ron de Bruin" wrote in message
...
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, befo=Num
End Sub

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


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



"LynneN" wrote in message
...
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?