Extending Excel with .Net Part III: Context Menus

[This was originally posted at http://timstall.dotnetdevelopersjournal.com/extending_excel_with_net_part_iii_context_menus.htm]

In my previous blog about extending Excel with .Net, I showed how an Excel sheet could call a .Net console app. This blog will show how to add Context Menus to a specific sheet. This is a little off topic for me as I don't normally discuss VBA. There are certainly other resources to cover this, like http://www.cpearson.com/excel.htm

What we'd like to do is add Context Menus for just a single workbook. When you close the books, the menus disappear, when you re-open it they reappear. Normally when you add an item to the context menu, it persists. So we'll add it when we open the workbook, and remove it when we deactivate the workbook. Something like so:

Private Const m_Context1 As String = "Context1" Private Const m_Context2 As String = "Context2" Private Sub Workbook_WindowActivate(ByVal Wn As Excel.Window)     Call AddItemToContextMenu End Sub Private Sub Workbook_WindowDeactivate(ByVal Wn As Excel.Window)     Call RemoveContextMenuItem End Sub Sub AddItemToContextMenu()     Dim cmdNew As CommandBarButton         Set cmdNew = Application.CommandBars("Cell").Controls.Add     With cmdNew         .Caption = m_Context1         .OnAction = "Method1"         .BeginGroup = True     End With     Set cmdNew = Application.CommandBars("Cell").Controls.Add     With cmdNew         .Caption = m_Context2         .OnAction = "Method2"         .BeginGroup = False     End With End Sub Sub RemoveContextMenuItem()     On Error Resume Next     Application.CommandBars("Cell").Controls(m_Context1).Delete     Application.CommandBars("Cell").Controls(m_Context2).Delete End Sub

A couple notes:

1. We reference the menu items by a string, so I refactor this to a constant such that string doesn't get accidentally mistyped during the remove phase.
2. The WindowActivate and WindowDeactive trigger the events we need. We can't merely just do this on open or close because the user may have two workbooks open at a time, and we don't want our context menu items to appear in the other workbook.
3. The OnAction property is set to the method to be called.

While it's nice to be able to add such menu items, it really makes me appreciate WinForms in .Net!

Next post: How to read the Excel book from .Net.