Design Comments or Macro to Write Macro Needed

[Eldrod]

I just finished a macro to look through a named range on a worksheet and add or remove a cell pattern based on a "hide" or "show" code in the first column. This macro is called by the Workbook_SheetActivate and Workbook_SheetChange event handlers coded in the macro's ThisWorkbook sheet. So when ever a cell is changed, the macro is called and resets the pattern based on any changes to the hide or show codes. This seems to work well when I place test code in the macro's (.xlsm) file. However, once I save this as an .xlam file and install it as an addin, it does not function when a new spreadsheet is loaded.

If I manually add the Workbook_SheetActivate and Workbook_SheetChange events to this new spreadsheet's Thisworkbook sheet, then it does apprear to try to call the macro.

So the questions I have are:
1. Is there some reason why the calls in the macro's ThisWorkbook sheet are not functioning (or is that by design) when running as an Addin macro? (I have other code for Workbook_Open in that macro that works, but it only gets called on open, not every change...)

2. Can I progammatically open the new spreadsheet's ThisWorkbook sheet and write the Sub Workbook_SheetChange macro (it's only about 3 lines)? It's OK if that programmatically built code is lost when the file is closed -- in fact, I would like to remove it on close if it doesn't.

Does anyone know how to do that or have a better idea for what I'm trying to accomplish?

Thanks!

 

[Eldrod]

I don't believe I can and have the process function as I need. The way this code is expected to work is that I open a spreadsheet and the user runs the macro "simulator" from the Add-ins. My simulator code looks through the spreadsheet and sets some formatting based on finding certain dynamic strings. However, when the user makes a formula change, that dynamic string might change, so I need to fire the simulator anytime a change is made to the sheet to refresh the formatting. Originally, I had those events Workbook_SheetChange and Workbook_SheetCalculate coded in the Addin's Thisworkbook sheet, but they would never fire. After Trial & Error and research, I figured out that these event triggers have to be in the calling spreadsheet's ThisWorkbook sheet. So the addin code now writes the event trigger code into the calling spreadsheet when the simulator is called, and removes all of the event triggers when the file is closed.
Is there a better way to accomplish this?