How to: Add Commands to Shortcut Menus in Excel

This example demonstrates how to add a command to a shortcut menu in Excel by using an application-level add-in. The shortcut menu appears when you right-click a worksheet cell. When the end user clicks the command, the text contained in all selected cells is written to a text file.

Applies to: The information in this topic applies to document-level projects and application-level projects for Excel 2007 and Excel 2010. For more information, see Features Available by Office Application and Project Type.

Add the following code to the ThisAddIn class in an application-level add-in project for Excel.

Example

Private WithEvents writeToText As Office.CommandBarButton
Private selectedCells As Excel.Range

Private Sub ThisAddIn_Startup(ByVal sender _
    As Object, ByVal e As System.EventArgs) Handles Me.Startup

    DefineShortcutMenu()
End Sub

Private Sub DefineShortcutMenu()

    Dim menuItem As Office.MsoControlType = Office.MsoControlType.msoControlButton
    writeToText = Application.CommandBars("Cell").Controls.Add(Type:=menuItem, _
        Before:=1, Temporary:=True)

    writeToText.Style = Office.MsoButtonStyle.msoButtonCaption
    writeToText.Caption = "Write to a Text File"
    writeToText.Tag = "0"
End Sub

Private Sub Application_SheetBeforeRightClick(ByVal Sh _
    As Object, ByVal Target As Excel.Range, _
    ByRef Cancel As Boolean) Handles Application.SheetBeforeRightClick

    selectedCells = Target
End Sub

Private Sub writeToText_Click(ByVal Ctrl As Office.CommandBarButton, _
    ByRef CancelDefault As Boolean) Handles writeToText.Click

    Try
        Dim currentDateTime As System.DateTime = _
            System.DateTime.Now
        Dim dateStamp As String = _
            currentDateTime.ToString("dMMMMyyyy_hh.mm.ss")

        Dim fileName As String = System.Environment.GetFolderPath( _
            Environment.SpecialFolder.MyDocuments) & "\\" & _
            dateStamp & ".txt"
        Dim sw As System.IO.StreamWriter = New System.IO.StreamWriter(fileName)

        For Each cell As Excel.Range In selectedCells.Cells
            If cell.Value2 IsNot Nothing Then
                sw.WriteLine(cell.Value2.ToString())
            End If
        Next
        sw.Close()
    Catch ex As Exception
        System.Windows.Forms.MessageBox.Show(ex.Message)
    End Try

End Sub

private Office.CommandBarButton writeToText;
private Excel.Range selectedCells;

private void ThisAddIn_Startup(object sender, System.EventArgs e)
{
    DefineShortcutMenu();
    Application.SheetBeforeRightClick +=
        new Excel.AppEvents_SheetBeforeRightClickEventHandler
            (Application_SheetBeforeRightClick);
    writeToText.Click +=
        new Microsoft.Office.Core._CommandBarButtonEvents_ClickEventHandler
            (writeToText_Click);
}

void writeToText_Click(Office.CommandBarButton Ctrl,
    ref bool CancelDefault)
{
    try
    {
        System.DateTime currentDateTime = System.DateTime.Now;
        string dateStamp = currentDateTime.ToString("dMMMMyyyy_hh.mm.ss");

        string fileName =
            System.Environment.GetFolderPath
            (Environment.SpecialFolder.MyDocuments) + "\\\\" + dateStamp + ".txt";
        System.IO.StreamWriter sw = new System.IO.StreamWriter(fileName);

        foreach (Excel.Range cell in selectedCells.Cells)
        {
            if (cell.Value2 != null)
            {
                sw.WriteLine(cell.Value2.ToString());
            }
        }
        sw.Close();
    }
    catch (Exception ex)
    {
        System.Windows.Forms.MessageBox.Show(ex.Message);
    }
}
void Application_SheetBeforeRightClick(object Sh,
    Excel.Range Target, ref bool Cancel)
{
    selectedCells = Target;
}

private void DefineShortcutMenu()
{

    Office.MsoControlType menuItem = Office.MsoControlType.msoControlButton;
    writeToText = (Office.CommandBarButton)Application.CommandBars["Cell"].
        Controls.Add(menuItem, missing, missing, 1, true);

    writeToText.Style = Office.MsoButtonStyle.msoButtonCaption;
    writeToText.Caption = "Write to a Text File";
    writeToText.Tag = "0";
}

Robust Programming

You must set the Tag property of your controls when you add event handlers. Office uses the Tag property to keep track of event handlers for a specific CommandBarControl. If the Tag property is blank, the events are not handled properly.

Declare your menu variables at the class level instead of inside the method where they are called. This ensures that the menu variables will remain in scope as long as the application is running. Otherwise, the item is removed by garbage collection, and your event handler code stops working.

See Also

Tasks

How to: Create Office Toolbars

Walkthrough: Creating Shortcut Menus for Bookmarks

How to: Add Commands to Shortcut Menus in Word

How to: Add Custom Menus and Menu Items to Outlook

How to: Add Custom Icons to Toolbar and Menu Items

Concepts

Optional Parameters in Office Solutions

Other Resources

Office UI Customization