Create an Excel Shortcut Menu That Writes Selected Values to a Text File (Harry Miller)

It doesn't take much code to add a command to a shortcut menu in Excel, but the menu objects have strange names so it's not intuitive, to me at least. The trickiest part is just sorting out how it's supposed to work, because it seems like it should be different from adding buttons to toolbars, but it really looks like that's what you're doing.

You can watch the video and make me happy, or you can just jump to the code example that's all given a little lower down. Thanks to McLean Schofield, programmer/writer and 3-star forum answer person, for this code example!

Duration: 3 minutes, 50 seconds.

Public Class ThisAddIn

    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 Microsoft.Office.Interop.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

End Class