Event Handling for CommandButton1 in very many (but not all) Worksheets

I have a CommandButton1 (at least) on every worksheet in a workbook.
Very many (but not all) of these CommandButtons have exactly the same handling code. How am I best structuring the code to avoid cutting & pasting the same handling code into very many worksheets?
Do I have to have at least the CommandButton1_Clicked() event handler implemented in each of the Worksheet code modules, but then each re-directs to a shared (single) implementation of the handler in the Module code area, or is there a better way? That still involves copying and pasting the skeletal handling code to very many worksheets.

Thanks in advance for any advice. I have tried searching the web but haven't found this issue described.

MathUKteacher

Hi MathUKTeacher,

There are two types of CommandButtons you can use on a Worksheet:

a. Active X Command Buttons which you seem to be using. When you create an Active X command button, Excel assigns a name to the Command Button such as CommandButton1. If you want a Macro associated with that specific CommandButton, you have to use the Macro name for that specific Command Button preassigned by Excel e.g. 'CommandButton1_Click(). That macro must be located in the 'Sheet Module' for the Sheet on which the Command Button is located.

b. Forms Command Buttons. Forms Command Buttons are 'Shapes' (such as a rectangle or an oval) that Excel automatically makes look like Command Buttons. Excel assigns names to each 'Shape' also. Forms Command Button names usually don't matter.

The advantage of a Forms Command Button is that the User can assign the Macro name to be associated with a Forms Command Button. Macros for Forms Command Buttons are located in 'Ordinary Code Modules' such as 'Module1'. The same Macro can be assigned to more than one Forms Command Button.

--------------------

If a design includes Active X Command Buttons and you want the same Macro code associated with more than one Command Button you can use the following type construction:

Please Login or Register  to view this content.

See the example file (Excel 2003) which demonstrates the above concepts for both 'Active X' and 'Forms' Command Buttons on several sheets. Please note that I have had problems in the past with 'Active X' Command Buttons on downloaded files. If the 'Active X' command buttons do not work in the Example file, it is probably due to an unwanted side effect caused by Excel and not by me.

Lewis