Adding code to new excel sheet dynamically

krishna picture krishna · Feb 26, 2013 · Viewed 9.5k times · Source

I need some help Regarding VBA. In My code I add an excel sheet and rename it and add a validation list to it.I need to run some code on changing value in that validation list.And that must run only on change of that particular cell. If I am not clear please let me know.Please Help me solving this.

Answer

Peter Albert picture Peter Albert · Feb 26, 2013

Instead of trying to create the individual code for each new worksheet with the Visual Basic Extensibility (see this link for further reading), simply use the Workbook wide event Workbook_SheetChange (you need to place it in the ThisWorkbookmodule).

In this event code first check, if the worksheet which caused the event is one of the newly created worksheets. This can be done most easily, be checking the .Name of the worksheet.