Create A Dynamic Workbook Collection
Posted by Derek Mang on October 1st, 2008
I have been developing an addin product that provides a dynamic user popup menu on a specific report worksheet. The popup menu is made available once a report has been created to allow for simple cell and range updating based on menu choices. The file can of course be saved for future use as one might expect.
So as things were moving along, with the addin in place I opened up a saved file with the report in question and found that report worksheet provided the standard cell context popup-menu rather than the popup-menu I was looking for!
Next I went googling for anything resembling recognition of a new workbook being opened and came up empty - except for the understanding that the workbook deactivate event from an already-open workbook fires after the new workbook file is opened.
What a stroke of luck!!
I had some code for a simple workbook class, to which I added code to the deactivate event. This code is used to set up a new collection of workbooks, and since the new one is open before the deactivate fires, it’s included.
The activate event is used to reset the collection as well. This is important when a workbook is closed to ensure that the collection is rebuilt to include only the open workbooks.
The custom popup-menu is also defined from within the activate event. Since all open workbooks are within the collection, switching from one to another will result in these events being fired, and hence the custom popup.
Open up the sample workbook. Right-click on the “Sheet1″ worksheet to see the popup. It’s only available on “Sheet1″. Open another workbook or add a new workbook. If there’s a “Sheet1″ present, you should get the custom popup for a cell or cell range.
What fun this is ….