1

I am creating an excel Add-In (.xlam) that needs to selectively respond to workbook/worksheet events, in all open workbooks (user may have multiple workbooks open). I dont want to go down the road of pasting code into other open workbook's ThisWorkbook modules.

I would like the user to be able to select whether the Add-In will respond to the active workbook's events.

What would be the most appropriate solution to achieve this?

Currently, I am thinking of a solution that looks like this:

Enable the Add-In to listen to application level events via a cls_AppEvents class (cls_AppEvents is instantiated on Workbook_open()).

When user clicks button, add the ActiveWorkbook.Name to an array of stings. When user clicks button again, remove the 'ActiveWorkbook.Name` from the array.(This would need to be a dynamic array - is there a better option?)

When application-level TableUpdate (for example) event fires, check the array of workbook names to see if it contains the Workbook's name where the event originated. If so, call whatever function.

Ed Wilson
  • 267
  • 3
  • 12
  • there are globals for thisaddin. `.Application.ActiveWorkbook` – Nathan_Sav Apr 06 '17 at 11:05
  • 1
    For better performance, you could use a Collection instead of an Array and store references to the worksheet objects instead of their name. – z32a7ul Apr 06 '17 at 11:39
  • @Nathan_Sav could you elaborate? – Ed Wilson Apr 06 '17 at 11:59
  • Have the add-in inspect a `CustomProperty` of the workbook. If the property is present and set to enable events, have the add-in act on the events for that workbook, if it is absent or unset, don't act on events for that workbook. Have the add-in offer functionality for adding/configuring the `CustomProperty` for any existing or new workbook. – ThunderFrame Apr 06 '17 at 12:51
  • you need to read about it, this was the 1st result on google http://stackoverflow.com/questions/7916711/get-the-current-workbook-object-in-c-sharp – Nathan_Sav Apr 06 '17 at 15:47

0 Answers0