Customize the right-click menu
 

I am using Office 2003 on Windows XP.

I just want to add a single custom control as the first choice on Excel's
built-in right-click shortcut spreadsheet menu ("Cell").

I want all the functions to work as normal, but I only want it to work in
the current file. Since changing the menu affects all files, I think the menu
needs to be rebuilt with my custom control (a button) at the top.

Can anyone offer any suggestions on how to do this? Please post example code.

Customize the right-click menu
 

quartz,

Here' how I do it:

In the ThisWorkBook module add these three procedures:

Private Sub Workbook_Open()
Call AddShortcutMenuItems 'install custom menu items
End Sub

Private Sub Workbook_Activate()
Call AddShortcutMenuItems 'install custom menu items
End Sub

Private Sub Workbook_Deactivate()
Call DeleteShortcutMenuItems ' when closing or moving to other workbook, if
close cancelled then this doesn't happen
End Sub

Now in a regular module, add these two procedures. I'd call this module
something like MenuMacros:

Sub AddShortcutMenuItems()
Dim new_menu_item As CommandBarButton
Dim i As Integer

Call DeleteShortcutMenuItems 'in case the items already there

'Add items to the Cell shortcut menu
With Application.CommandBars("Cell")
Set new_menu_item = .Controls.Add(Type:=msoControlButton, befo=1)
With new_menu_item
.Caption = "&Test Button"
.OnAction = "test_button_macro"
.Style = msoButtonIconAndCaption
.FaceId = 8
End With
End With
End Sub

Sub DeleteShortcutMenuItems()

On Error Resume Next 'in case the item isn't there

With Application.CommandBars("Cell")
.Controls("&Test Button").Delete
End With
End Sub

Now add the button click procedure - I'd do it in a 3rd module but it could
be in the menu module too:

Sub test_button_macro()
msgbox "testing"
End Sub

hth,

Doug

"quartz" wrote in message
...
I am using Office 2003 on Windows XP.

I just want to add a single custom control as the first choice on Excel's
built-in right-click shortcut spreadsheet menu ("Cell").

I want all the functions to work as normal, but I only want it to work in
the current file. Since changing the menu affects all files, I think the
menu
needs to be rebuilt with my custom control (a button) at the top.

Can anyone offer any suggestions on how to do this? Please post example
code.

Customize the right-click menu
 

In the 'ThisWorkbook' module of your file put...
'/==================================================/
Private Sub Workbook_Activate()
'create menu item on at top of Cell menu
' [Right click on a cell]
Dim cbcMenuItem As CommandBarControl
On Error Resume Next

Set cbcMenuItem = _
Application.CommandBars("CELL").Controls. _
Add(Type:=1, Befo=1, Temporary:=True)
'Type 1 = msoControlButton

With cbcMenuItem
.Caption = "&My_Menu_Item"
.OnAction = ThisWorkbook.Name & "!My_Macro"
End With

Set cbcMenuItem = Nothing

End Sub

'/==================================================/
Private Sub Workbook_Deactivate()
'delete menu item on Cell menu
Dim strCaption As String

On Error Resume Next

strCaption = "&My_Menu_Item"

Application.CommandBars("CELL"). _
Controls(strCaption).Delete

End Sub
'/==================================================/

In a regular module, put your macro
Example:
'/==================================================/
Public Sub My_Macro()
MsgBox "Hello"
End Sub
'/==================================================/

- - - - -
Items that you can change.
Caption of "&My_Menu_Item" can be changed to anything you want
OnAction points to the macro in the regular module. You can change
'My_Macro' to something else

HTH,
Gary Brown





"quartz" wrote:

I am using Office 2003 on Windows XP.

I just want to add a single custom control as the first choice on Excel's
built-in right-click shortcut spreadsheet menu ("Cell").

I want all the functions to work as normal, but I only want it to work in
the current file. Since changing the menu affects all files, I think the menu
needs to be rebuilt with my custom control (a button) at the top.

Can anyone offer any suggestions on how to do this? Please post example code.

Customize the right-click menu
 

Thanks Gary! It works well. I took the liberty of just using the "Reset"
command when the workbook is deactivated rather than deleting the control
(the effect is the same).

Thanks much.

"Gary Brown" wrote:

In the 'ThisWorkbook' module of your file put...
'/==================================================/
Private Sub Workbook_Activate()
'create menu item on at top of Cell menu
' [Right click on a cell]
Dim cbcMenuItem As CommandBarControl
On Error Resume Next

Set cbcMenuItem = _
Application.CommandBars("CELL").Controls. _
Add(Type:=1, Befo=1, Temporary:=True)
'Type 1 = msoControlButton

With cbcMenuItem
.Caption = "&My_Menu_Item"
.OnAction = ThisWorkbook.Name & "!My_Macro"
End With

Set cbcMenuItem = Nothing

End Sub

'/==================================================/
Private Sub Workbook_Deactivate()
'delete menu item on Cell menu
Dim strCaption As String

On Error Resume Next

strCaption = "&My_Menu_Item"

Application.CommandBars("CELL"). _
Controls(strCaption).Delete

End Sub
'/==================================================/

In a regular module, put your macro
Example:
'/==================================================/
Public Sub My_Macro()
MsgBox "Hello"
End Sub
'/==================================================/

- - - - -
Items that you can change.
Caption of "&My_Menu_Item" can be changed to anything you want
OnAction points to the macro in the regular module. You can change
'My_Macro' to something else

HTH,
Gary Brown





"quartz" wrote:

I am using Office 2003 on Windows XP.

I just want to add a single custom control as the first choice on Excel's
built-in right-click shortcut spreadsheet menu ("Cell").

I want all the functions to work as normal, but I only want it to work in
the current file. Since changing the menu affects all files, I think the menu
needs to be rebuilt with my custom control (a button) at the top.

Can anyone offer any suggestions on how to do this? Please post example code.

Customize the right-click menu
 

The only issue with 'Reset' is when you have other customized button(s) on
your menu. Resetting wipes out the other button(s) (of yours or from another
add-in). It's kind of a shotgun approach. Deleting the specific button is
the handgun approach.
Either method seems fine for what you want to do.
Good Luck.
Sincerely,
Gary Brown

"quartz" wrote:

Thanks Gary! It works well. I took the liberty of just using the "Reset"
command when the workbook is deactivated rather than deleting the control
(the effect is the same).

Thanks much.

"Gary Brown" wrote:

In the 'ThisWorkbook' module of your file put...
'/==================================================/
Private Sub Workbook_Activate()
'create menu item on at top of Cell menu
' [Right click on a cell]
Dim cbcMenuItem As CommandBarControl
On Error Resume Next

Set cbcMenuItem = _
Application.CommandBars("CELL").Controls. _
Add(Type:=1, Befo=1, Temporary:=True)
'Type 1 = msoControlButton

With cbcMenuItem
.Caption = "&My_Menu_Item"
.OnAction = ThisWorkbook.Name & "!My_Macro"
End With

Set cbcMenuItem = Nothing

End Sub

'/==================================================/
Private Sub Workbook_Deactivate()
'delete menu item on Cell menu
Dim strCaption As String

On Error Resume Next

strCaption = "&My_Menu_Item"

Application.CommandBars("CELL"). _
Controls(strCaption).Delete

End Sub
'/==================================================/

In a regular module, put your macro
Example:
'/==================================================/
Public Sub My_Macro()
MsgBox "Hello"
End Sub
'/==================================================/

- - - - -
Items that you can change.
Caption of "&My_Menu_Item" can be changed to anything you want
OnAction points to the macro in the regular module. You can change
'My_Macro' to something else

HTH,
Gary Brown





"quartz" wrote:

I am using Office 2003 on Windows XP.

I just want to add a single custom control as the first choice on Excel's
built-in right-click shortcut spreadsheet menu ("Cell").

I want all the functions to work as normal, but I only want it to work in
the current file. Since changing the menu affects all files, I think the menu
needs to be rebuilt with my custom control (a button) at the top.

Can anyone offer any suggestions on how to do this? Please post example code.

Customize the right-click menu
 

Gary,

I saw this posting and it does exactly what I need with one exception. How
do I sort the list or place my special menu item in a certain position (i.e.
2nd from top)

Thanks

"Gary Brown" wrote:

In the 'ThisWorkbook' module of your file put...
'/==================================================/
Private Sub Workbook_Activate()
'create menu item on at top of Cell menu
' [Right click on a cell]
Dim cbcMenuItem As CommandBarControl
On Error Resume Next

Set cbcMenuItem = _
Application.CommandBars("CELL").Controls. _
Add(Type:=1, Befo=1, Temporary:=True)
'Type 1 = msoControlButton

With cbcMenuItem
.Caption = "&My_Menu_Item"
.OnAction = ThisWorkbook.Name & "!My_Macro"
End With

Set cbcMenuItem = Nothing

End Sub

'/==================================================/
Private Sub Workbook_Deactivate()
'delete menu item on Cell menu
Dim strCaption As String

On Error Resume Next

strCaption = "&My_Menu_Item"

Application.CommandBars("CELL"). _
Controls(strCaption).Delete

End Sub
'/==================================================/

In a regular module, put your macro
Example:
'/==================================================/
Public Sub My_Macro()
MsgBox "Hello"
End Sub
'/==================================================/

- - - - -
Items that you can change.
Caption of "&My_Menu_Item" can be changed to anything you want
OnAction points to the macro in the regular module. You can change
'My_Macro' to something else

HTH,
Gary Brown





"quartz" wrote:

I am using Office 2003 on Windows XP.

I just want to add a single custom control as the first choice on Excel's
built-in right-click shortcut spreadsheet menu ("Cell").

I want all the functions to work as normal, but I only want it to work in
the current file. Since changing the menu affects all files, I think the menu
needs to be rebuilt with my custom control (a button) at the top.

Can anyone offer any suggestions on how to do this? Please post example code.

Customize the right-click menu
 

Hi Rafi,

Change...
Set cbcMenuItem = _
Application.CommandBars("CELL").Controls. _
Add(Type:=1, Befo=1, Temporary:=True)

To...
Set cbcMenuItem = _
Application.CommandBars("CELL").Controls. _
Add(Type:=1, Befo=1, Temporary:=True, Befo=2)

--
HTH,
Gary Brown

If this post was helpful to you, please select ''YES'' at the bottom of the
post.



"Rafi" wrote:

Gary,

I saw this posting and it does exactly what I need with one exception. How
do I sort the list or place my special menu item in a certain position (i.e.
2nd from top)

Thanks

"Gary Brown" wrote:

In the 'ThisWorkbook' module of your file put...
'/==================================================/
Private Sub Workbook_Activate()
'create menu item on at top of Cell menu
' [Right click on a cell]
Dim cbcMenuItem As CommandBarControl
On Error Resume Next

Set cbcMenuItem = _
Application.CommandBars("CELL").Controls. _
Add(Type:=1, Befo=1, Temporary:=True)
'Type 1 = msoControlButton

With cbcMenuItem
.Caption = "&My_Menu_Item"
.OnAction = ThisWorkbook.Name & "!My_Macro"
End With

Set cbcMenuItem = Nothing

End Sub

'/==================================================/
Private Sub Workbook_Deactivate()
'delete menu item on Cell menu
Dim strCaption As String

On Error Resume Next

strCaption = "&My_Menu_Item"

Application.CommandBars("CELL"). _
Controls(strCaption).Delete

End Sub
'/==================================================/

In a regular module, put your macro
Example:
'/==================================================/
Public Sub My_Macro()
MsgBox "Hello"
End Sub
'/==================================================/

- - - - -
Items that you can change.
Caption of "&My_Menu_Item" can be changed to anything you want
OnAction points to the macro in the regular module. You can change
'My_Macro' to something else

HTH,
Gary Brown





"quartz" wrote:

I am using Office 2003 on Windows XP.

I just want to add a single custom control as the first choice on Excel's
built-in right-click shortcut spreadsheet menu ("Cell").

I want all the functions to work as normal, but I only want it to work in
the current file. Since changing the menu affects all files, I think the menu
needs to be rebuilt with my custom control (a button) at the top.

Can anyone offer any suggestions on how to do this? Please post example code.

Customize the right-click menu
 

There should be two Before Arguments?

--
Regards,
Tom Ogilvy


"Gary Brown" wrote in
message ...
Hi Rafi,

Change...
Set cbcMenuItem = _
Application.CommandBars("CELL").Controls. _
Add(Type:=1, Befo=1, Temporary:=True)

To...
Set cbcMenuItem = _
Application.CommandBars("CELL").Controls. _
Add(Type:=1, Befo=1, Temporary:=True, Befo=2)

--
HTH,
Gary Brown

If this post was helpful to you, please select ''YES'' at the bottom of
the
post.



"Rafi" wrote:

Gary,

I saw this posting and it does exactly what I need with one exception.
How
do I sort the list or place my special menu item in a certain position
(i.e.
2nd from top)

Thanks

"Gary Brown" wrote:

In the 'ThisWorkbook' module of your file put...
'/==================================================/
Private Sub Workbook_Activate()
'create menu item on at top of Cell menu
' [Right click on a cell]
Dim cbcMenuItem As CommandBarControl
On Error Resume Next

Set cbcMenuItem = _
Application.CommandBars("CELL").Controls. _
Add(Type:=1, Befo=1, Temporary:=True)
'Type 1 = msoControlButton

With cbcMenuItem
.Caption = "&My_Menu_Item"
.OnAction = ThisWorkbook.Name & "!My_Macro"
End With

Set cbcMenuItem = Nothing

End Sub

'/==================================================/
Private Sub Workbook_Deactivate()
'delete menu item on Cell menu
Dim strCaption As String

On Error Resume Next

strCaption = "&My_Menu_Item"

Application.CommandBars("CELL"). _
Controls(strCaption).Delete

End Sub
'/==================================================/

In a regular module, put your macro
Example:
'/==================================================/
Public Sub My_Macro()
MsgBox "Hello"
End Sub
'/==================================================/

- - - - -
Items that you can change.
Caption of "&My_Menu_Item" can be changed to anything you want
OnAction points to the macro in the regular module. You can change
'My_Macro' to something else

HTH,
Gary Brown





"quartz" wrote:

I am using Office 2003 on Windows XP.

I just want to add a single custom control as the first choice on
Excel's
built-in right-click shortcut spreadsheet menu ("Cell").

I want all the functions to work as normal, but I only want it to
work in
the current file. Since changing the menu affects all files, I think
the menu
needs to be rebuilt with my custom control (a button) at the top.

Can anyone offer any suggestions on how to do this? Please post
example code.

Customize the right-click menu
 

Good catch Tom :O

To...
Set cbcMenuItem = _
Application.CommandBars("CELL").Controls. _
Add(Type:=1, Temporary:=True, Befo=2)

--
HTH,
Gary Brown

If this post was helpful to you, please select ''YES'' at the bottom of the
post.



"Tom Ogilvy" wrote:

There should be two Before Arguments?

--
Regards,
Tom Ogilvy


"Gary Brown" wrote in
message ...
Hi Rafi,

Change...
Set cbcMenuItem = _
Application.CommandBars("CELL").Controls. _
Add(Type:=1, Befo=1, Temporary:=True)

To...
Set cbcMenuItem = _
Application.CommandBars("CELL").Controls. _
Add(Type:=1, Befo=1, Temporary:=True, Befo=2)

--
HTH,
Gary Brown

If this post was helpful to you, please select ''YES'' at the bottom of
the
post.



"Rafi" wrote:

Gary,

I saw this posting and it does exactly what I need with one exception.
How
do I sort the list or place my special menu item in a certain position
(i.e.
2nd from top)

Thanks

"Gary Brown" wrote:

In the 'ThisWorkbook' module of your file put...
'/==================================================/
Private Sub Workbook_Activate()
'create menu item on at top of Cell menu
' [Right click on a cell]
Dim cbcMenuItem As CommandBarControl
On Error Resume Next

Set cbcMenuItem = _
Application.CommandBars("CELL").Controls. _
Add(Type:=1, Befo=1, Temporary:=True)
'Type 1 = msoControlButton

With cbcMenuItem
.Caption = "&My_Menu_Item"
.OnAction = ThisWorkbook.Name & "!My_Macro"
End With

Set cbcMenuItem = Nothing

End Sub

'/==================================================/
Private Sub Workbook_Deactivate()
'delete menu item on Cell menu
Dim strCaption As String

On Error Resume Next

strCaption = "&My_Menu_Item"

Application.CommandBars("CELL"). _
Controls(strCaption).Delete

End Sub
'/==================================================/

In a regular module, put your macro
Example:
'/==================================================/
Public Sub My_Macro()
MsgBox "Hello"
End Sub
'/==================================================/

- - - - -
Items that you can change.
Caption of "&My_Menu_Item" can be changed to anything you want
OnAction points to the macro in the regular module. You can change
'My_Macro' to something else

HTH,
Gary Brown





"quartz" wrote:

I am using Office 2003 on Windows XP.

I just want to add a single custom control as the first choice on
Excel's
built-in right-click shortcut spreadsheet menu ("Cell").

I want all the functions to work as normal, but I only want it to
work in
the current file. Since changing the menu affects all files, I think
the menu
needs to be rebuilt with my custom control (a button) at the top.

Can anyone offer any suggestions on how to do this? Please post
example code.