Where does code go

[Aussiebear]

I often read posts where people ask "where does the code go". Could someone please give me a "Code Placement 101" explanation?

For example, in aoc's thread "Adding Date with Double Click", Simon reinforced the point that the code needed to go on the Worksheet module not a standard module.

Am I correct in assuming then that any code which is placed in the workbook module is available to the workbook, that code placed in a worksheet module is limited to that sheet, and that code placed in a standard module available to the user in any sheet?

[johnske]

...Am I correct in assuming then that any code which is placed in the workbook module is available to the workbook, that code placed in a worksheet module is limited to that sheet, and that code placed in a standard module available to the user in any sheet?

Yes, if you see (for example) [vba]Private Sub Workbook_SheetActivate(ByVal Sh As Object)[/vba]then that is workbook event code and it goes in the ThisWorkbook code module, the event code is then run every time any sheet is activated (i.e. worksheet or chartsheet), whereas if you see [vba]Private Sub Worksheet_Activate()[/vba]this is worksheet event code that goes into the code module of the sheet or sheets that you want it to apply to when that particular worksheet (or sheets) is activated...

For more, see here, here, and here

[Bob Phillips]

If you are writing workbook event code, it has to go in ThisWorkbook, otherwise it will not be triggered.

If you are writing worksheet event code, it has to go in the appropriate worksheet code module, otherwise it will not be triggered.

If you are writing userform event code, it has to go in userform code module, otherwise it will not be triggered.

Of course, it gets more difficult if you encapsulate some of the code within those events within a separate procedure. That procedure could go in the same code module, and stay private to that code module, or it could go n a general code module, and be made public to the whole project. The former has the advantage that all of the class properties (such as Me) are still exposed to the procedure, but it can obscure the code readability. The latter has the advantage that it can abstract code, making it simpler to test in isolation, keep the class module code very event specific, but does lose those class properties.

And it gets even more complex if you create object classes to deal with specific events, such as a form entry class that handles input on all textbox input on a form. This object class would be separate to the form class, but is inextricaly linked.