customize context menu

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Andy - 25 Mar 2004 02:11 GMT

I know how easy it is to customize toolbars and menu bars but that seems
to exclude the "right click" context menu. How can I do that?
I want to add a summation command and a data sort command, I use them a lot.

Reply to this Message

Gord Dibben - 25 Mar 2004 02:44 GMT

Andy

Done through VBA code. In the ThisWorkbook module.

Private Sub WorkBook_Open()
With Application.CommandBars("Cell").Controls.Add(temporary:=True)
.Caption = "Change Text Case"
.OnAction = "MyMacros.xla" & "!Chg_Case"
End With
End Sub

To clear the menu when you close. Otherwise you get duplicates.

Sub Workbook_BeforeClose(Cancel As Boolean)
Application.CommandBars("Cell").Controls("Change Text Case").Delete
End Sub

Place this code in your Personal.xls or in my case, the MyMacros add-in.

Gord Dibben Excel MVP

>I know how easy it is to customize toolbars and menu bars but that seems
>to exclude the "right click" context menu. How can I do that?
>I want to add a summation command and a data sort command, I use them a lot.

Reply to this Message

David McRitchie - 25 Mar 2004 18:17 GMT

Hi Andy,
You specifically asked for AutoSum -- don't know what the fascination is
about AutoSum on right click menus is but see this previous posting:
http://google.com/groups?threadm=ujkvPg0DEHA.3064%40tk2msftngp13.phx.gbl

I won't touch the sort part of your question, as I can't think of anything that
would be generic work in a wide range of situations and not mess up
data if used accidentally. At least not without particulars on exactly
what you want and why a right-click menu. Some help on using sort
http://www.mvps.org/dmcritchie/excel/sorting.htm#ctrla
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

Reply to this Message