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:
- 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.
- 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.
- 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.
links: digg this technorati
