Microsoft Excel - how to display a custom context menu in excel 2007

Asked By kris kumar
06-Nov-08 02:45 AM
   
  • Hi All,

    I am trying to show a custom menu when the user right mouse click on a cell. I have added some menu items but my menu items are appearing along with the default Right mouse menu's. How can I show my custom menu items only when the user click on a cell.

    My project is a Excel workbook project and I developed the application using c#.

    Please help
    kris

var beenDer = false; function pageLoad() { if(!beenDer) { $create(Microsoft.Com.Forums.MessagesPage, {data: pageData}); beenDer = true; } }

see this code, hope it helps you:  see this code, hope it helps you:

06-Nov-08 03:49 AM

In a separate module which have named "DMcRitchie_RClick" I have the following code:

Option Explicit

'see comments below
Sub GetFormula_sub()
If ActiveCell.Column = 1 Then
ActiveCell.Formula = "=personal.xls!GetFormula(" & _
ActiveCell.Offset(-1, 0).Address(0, 0) & ")"
MsgBox "couldn't use cell to left so setting to use from cell above"
Else
ActiveCell.Formula = "=personal.xls!GetFormula(" & _
ActiveCell.Offset(0, -1).Address(0, 0) & ")"
End If
End Sub

Sub EndTotal_sub()
Dim end_data As Long 'dmcritchie RClick 2005-05-28
Range(ActiveCell.Address, _
Cells(Rows.Count, ActiveCell.Column).End(xlUp).Address).Select
end_data = ActiveSheet.Cells(Rows.Count, ActiveCell.Column).End(xlUp).Row
ActiveSheet.Cells(end_data + 1, ActiveCell.Column).Formula = _
"=SUBTOTAL(9," & ActiveSheet.Cells(2, ActiveCell.Column).Address(1, 0) _
& ":OFFSET(" & ActiveSheet.Cells(end_data + 1, _
ActiveCell.Column).Address(0, 0) & ",-1,0))"
End Sub

Sub Clear_Constants() 'D.McRitchie RClick 2005-11-19 (also see insrtrow.htm)
'-- provide for in rightclick cell, row, and column commandbars
Dim rng As Range 'prevent expansion of a single cell selection
Set rng = Intersect(Selection, Selection.SpecialCells(xlConstants))
If rng Is Nothing Then
MsgBox "No constants in selection area(s) -- no removal"
Else
rng.ClearContents
End If
End Sub

'change to personal.xls if that is what you use
'see also ChipPearson_Module for Chip Pearson's RClick menus
'these are setup in module1 in an Auto_Open
'Documentation in: http://www.mvps.org/dmcritchie/excel/rightclick.htm



See this link:
http://www.mvps.org/dmcritchie/excel/rightclick.htm


Reply  Reply

06-Nov-08 03:54 AM
Option Explicit

Public Const APPNAME As String = "Custom Right-Click Menu"

Sub CreateToolsMenu()
Dim cb As CommandBar, cbMenu As CommandBarControl, cbMenuItem As CommandBarButton
Call DeleteToolsMenu
Set cb = Application.CommandBars("Worksheet Menu Bar")
Set cbMenu = cb.Controls("Tools")
Set cbMenuItem = cbMenu.Controls.Add(Type:=msoControlButton, before:=1)
cbMenuItem.Caption = APPNAME
cbMenuItem.State = msoButtonUp
cbMenuItem.OnAction = "Mod_Menu.CreateCellsMenu"
End Sub

Sub DeleteToolsMenu()
On Error Resume Next
Application.CommandBars("Tools").Controls(APPNAME).Delete
DeleteCellsMenu
End Sub

Sub CreateCellsMenu()
Dim cItem As CommandBarControl
Dim cb As CommandBarControl, cbMenuItem As CommandBarButton
Set cb = Application.CommandBars("Worksheet Menu Bar").Controls("Tools")
Set cbMenuItem = cb.Controls(APPNAME)
If cbMenuItem.State = msoButtonUp Then
For Each cItem In Application.CommandBars("Cell").Controls
Select Case cItem.Caption
Case "Cu&t", "&Copy", "&Paste", "&Delete..."
Case Else
cItem.Delete
End Select
Next cItem
cbMenuItem.State = msoButtonDown
Else
Application.CommandBars("Cell").Reset
cbMenuItem.State = msoButtonUp
End If
End Sub

Sub DeleteCellsMenu()
Application.CommandBars("Cell").Reset
End Sub

This will add an option to your Tools menu (right at the top) labeled Custom Right-Click Menu.
This acts as a toggle button, you can turn it on and off, thus creating
In your ThisWorkbook module put this code...

Option Explicit

Private Sub Workbook_AddinInstall()
Call CreateToolsMenu
End Sub

Private Sub Workbook_AddinUninstall()
Call DeleteToolsMenu
End Sub

Private Sub Workbook_Open()
Call CreateToolsMenu
End Sub
a custom menu (what you specified) and the normal (default) menu.





display a custom context menu in excel 2007  display a custom context menu in excel 2007

06-Nov-08 09:30 AM
In a standard module by the name of Mod_Menu put in this code...


Code:
Option Explicit

Public Const APPNAME As String = "Custom Right-Click Menu"

Sub CreateToolsMenu()
Dim cb As CommandBar, cbMenu As CommandBarControl, cbMenuItem As CommandBarButton
Call DeleteToolsMenu
Set cb = Application.CommandBars("Worksheet Menu Bar")
Set cbMenu = cb.Controls("Tools")
Set cbMenuItem = cbMenu.Controls.Add(Type:=msoControlButton, before:=1)
cbMenuItem.Caption = APPNAME
cbMenuItem.State = msoButtonUp
cbMenuItem.OnAction = "Mod_Menu.CreateCellsMenu"
End Sub

Sub DeleteToolsMenu()
On Error Resume Next
Application.CommandBars("Tools").Controls(APPNAME).Delete
DeleteCellsMenu
End Sub

Sub CreateCellsMenu()
Dim cItem As CommandBarControl
Dim cb As CommandBarControl, cbMenuItem As CommandBarButton
Set cb = Application.CommandBars("Worksheet Menu Bar").Controls("Tools")
Set cbMenuItem = cb.Controls(APPNAME)
If cbMenuItem.State = msoButtonUp Then
For Each cItem In Application.CommandBars("Cell").Controls
Select Case cItem.Caption
Case "Cu&t", "&Copy", "&Paste", "&Delete..."
Case Else
cItem.Delete
End Select
Next cItem
cbMenuItem.State = msoButtonDown
Else
Application.CommandBars("Cell").Reset
cbMenuItem.State = msoButtonUp
End If
End Sub

Sub DeleteCellsMenu()
Application.CommandBars("Cell").Reset
End Sub

This will add an option to your Tools menu (right at the top) labeled Custom Right-Click Menu. This acts as a toggle button, you can turn it on and off, thus creating a custom menu (what you specified) and the normal (default) menu.


In your ThisWorkbook module put this code...

Code:
Option Explicit

Private Sub Workbook_AddinInstall()
Call CreateToolsMenu
End Sub

Private Sub Workbook_AddinUninstall()
Call DeleteToolsMenu
End Sub

Private Sub Workbook_Open()
Call CreateToolsMenu
End Sub

As you can see this code is defaulted for an add-in, not a regular workbook. This is so it is there all the time. I have uploaded a sample file of this add-in. Put in a folder (I personally recommand C:\WINDOWS\ADDINS, as it's the easiest for me to remember rather than the Excel default folder, which is very hard to remember - for me). Then from Excel go to Tools | Add-ins | Browse, go to the folder and select the add-in. Now look under your Tools menu.
whats wrong with this code???  whats wrong with this code???
06-Nov-08 07:47 PM
thanks for all the help.

The pop up menu shows only for the first right mouse click and if I click again, I see the default context menu. Whats wrong with the following code???? please help/

 private void SheetBeforeRightClickHandler(object sender, Excel.Range target, ref bool cancel)
        
        {
       
            cancel = true;


            CommandBar commandBarTools = (Microsoft.Office.Core.CommandBar)m_excel.CommandBars.Add("Test", Microsoft.Office.Core.MsoBarPosition.msoBarPopup, missing, true);
            
                //CommandBar commandBarTools = (CommandBar)m_excel.Application.CommandBars["Cell"];

                //add Control Popup Item to the Tools Menu

                CommandBarControl cmdBarTools = commandBarTools.Controls.Add(MsoControlType.msoControlPopup, 1, "", 1, true);

                cmdBarTools.Visible = true;
        
                cmdBarTools.Caption = "My Tools";

                //add a Buy sub-option

                CommandBarPopup popup = (CommandBarPopup)cmdBarTools;
           

                CommandBar bar = popup.CommandBar;

                CommandBarControl cmdBarControl = bar.Controls.Add(MsoControlType.msoControlButton, 1, "", 1, true);

                cmdBarControl.Caption = "Buy";

                CommandBarButton button = (CommandBarButton)cmdBarControl;

                button.Click += new _CommandBarButtonEvents_ClickEventHandler(button_Click);



                //add a Sell sub-option

                CommandBarPopup popup2 = (CommandBarPopup)cmdBarTools;

                CommandBar bar2 = popup2.CommandBar;

                CommandBarControl cmdBarControl2 = bar.Controls.Add(MsoControlType.msoControlButton, 1, "", 1, true);

                cmdBarControl2.Caption = "Sell";

                CommandBarButton button2 = (CommandBarButton)cmdBarControl2;

                button2.Click += new _CommandBarButtonEvents_ClickEventHandler(button2_Click);
                commandBarTools.ShowPopup(missing, missing);


        }
Uploaded file  Uploaded file
10-Sep-09 05:46 PM
I know this was some time ago, but do you have the file still you mentioned you uploaded (the add-in).  I am going to make the same adjustment for myself and would greatly appreciate seeing your example.  Thanks.

DRC
Create New Account
help
SharePoint Products and Technologies Configuration Wizard fails during install SharePoint Trying to reinstall Office Sharepoint Server 2007 and fails during SharePoint Products and Technologies Configuration Wizard routine SharePoint Setup Discussions SharePoint (1) At what step? and what is
Configuration of SharePoint Products and Technologies failed. Project Hi: I tried to migrate Project Server 2003 to Project Server 2007 NET framework 3.0 then with SP 1, all ran successfully. Then I installed Windows SharePoint V3 (with inline upgrade of V2.0) then SP1, I also ran SharePoint Products and Technologies configuration wizard (finished all 10 steps)and finished successfully. So I proceed to install Project Server 2007, it ran succeessfully, however, after the installation I ran the Sharepoint Products and Technologies Configuration Wizard, it ran up 8 of 9 steps witout problem, but
SharePoint Server 2007 Installation Error SharePoint I am installing Sharepoint Server 2007 on Server 2003 R2 and the the SharePoint Products and Technologies Configuration Wizard fails with: The SharePoint Products and Technologies Configuration Wizard encountered an error and must close. Launch the wizard again from
Error after runing the configuration of sharepoint products and technologies SharePoint hi, After installing Sharepoint 2007 and running the configuration of sharepoint products and technologies i got this error how to solve this: One or more configuration settings failed
SharePoint Products and Technologies Configuration Wizard problem SharePoint Hi I am trying to install wss3.0 on windows server. I run the setup and at the end i get something like the following: Click close and run SharePoint Products and Technologies Configuration Wizard from Start Menu." I click close and then it tells me i don't have the wizard in the start menu. What could be the problem? Thanks SharePoint Setup Discussions SharePoint Products and Technologies (1) TasksI (1) Alpesh (1) Nakar (1) Directory (1