Customize the right-click menu

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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.
 
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, before:=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 said:
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
Click to expand...
code.
 
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, Before:=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
 
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 said:
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, Before:=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 said:
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.
Click to expand...
Click to expand...
 
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 said:
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 said:
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, Before:=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 said:
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.
Click to expand...
Click to expand...
Click to expand...
 
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 said:
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, Before:=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 said:
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.
Click to expand...
Click to expand...
 
Hi Rafi,

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

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

--
HTH,
Gary Brown
(e-mail address removed)
If this post was helpful to you, please select ''YES'' at the bottom of the
post.



Rafi said:
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 said:
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, Before:=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 said:
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.
Click to expand...
Click to expand...
Click to expand...
 
There should be two Before Arguments?

--
Regards,
Tom Ogilvy


Gary Brown said:
Hi Rafi,

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

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

--
HTH,
Gary Brown
(e-mail address removed)
If this post was helpful to you, please select ''YES'' at the bottom of
the
post.



Rafi said:
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 said:
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, Before:=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





:

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.
Click to expand...
Click to expand...
Click to expand...
 
Good catch Tom :O>

--
HTH,
Gary Brown
(e-mail address removed)
If this post was helpful to you, please select ''YES'' at the bottom of the
post.



Tom Ogilvy said:
There should be two Before Arguments?

--
Regards,
Tom Ogilvy


Gary Brown said:
Hi Rafi,

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

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

--
HTH,
Gary Brown
(e-mail address removed)
If this post was helpful to you, please select ''YES'' at the bottom of
the
post.



Rafi said:
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

:

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, Before:=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





:

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.
Click to expand...
Click to expand...
Click to expand...
 
You must log in or register to reply here.

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Custom right-click menu without changing Excel.App 1
add "paste values" to right click menu 6
Remove custom menu 1
password to access a custom menu? 1
Displaying A custom menu as a popup menu 5
right click menu 2
Excel 2007 & Custom Shortcut Menues 6
Custom Menu Bar in Excel 2007 21

Back
Top