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:
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.