Microsoft Excel Object

[thtang]

I know that if I need to write a function or sub, I would need to write it in the module. But I am curious that what stuff to put in the Microsoft Excel Objects and when would we use it?

Thank you.

[AlvaroSiza]

Here's what the pro (C. Pearson) says...

"As a general rule, the sheet modules and the ThisWorkbook module should contain only event procedures, and nothing else. All other code should reside in regular code modules. Some would argue convincingly that private procedures used only by the event procedures in a module should be contained in the same sheet/thisworkbook module in which they are used. There is no practical limit to the number of modules allowed in a project, so you should organize your code into multiple modules, based on the type of functionality provided by the code."

Draw an activeX object on your sheet and, with design mode on, double-click it. You will see that you were taken to the worksheet level, rather than a sub. Clicks, double-clicks, cell changes, worksheet activations,...action-oriented events go within worksheets. Often, your code within the worksheet (for, say, a command button) will merely contain a Call to a procedure located within a module.

Hope this helps.

(BTW - The quote above was the first link of a "when to code in worksheet vs. sub" google search. Google is your friend <most of the time>)