Can I add a macro dynamically?

[Stoffel24]

Hi, is there a way of inserting a new sheet and then adding a macro to that sheet. I wish to be able to add a sheet and I then put a number on that sheet. Depending on what that number is, something must happen. I need a macro to fire when the value on the newly added sheet is changed by the user. Any ideas?

 

[RobBroekhuis]

Stoffel,
You can add a code module to a sheet programmatically
(check out the methods of the
thisworkbook.vbproject.vbcomponents().codemodule object) but I think maybe you're better off handling that through the ThisWorkbook code module's Workbook_Sheetchange event handler. Inside the event handler, check if the name of the sheet is the one you just added (you'll need a public variable for that) and if the address of the changed cell (target.address) is the one for which you need to trigger your code. If you need more help with either approach, please check back.
Rob