+ Reply to Thread
Results 1 to 2 of 2

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

  1. #1
    Registered User
    Join Date
    MS-Off Ver
    Office 10

    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.


  2. #2
    Forum Guru
    Join Date
    MS-Off Ver
    Microsoft 365 aka Office 365

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

    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.


+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Excel VBA event handling
    By vangxbg in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 04-06-2013, 03:25 PM
  2. event handling froma userform
    By jad70 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 03-10-2009, 09:15 AM
  3. [SOLVED] checkbox event handling function
    By nevwalters in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 05-15-2006, 11:35 PM
  4. On Event calendar with error handling?
    By Chip in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 08-22-2005, 07:05 PM
  5. [SOLVED] Event handling...
    By Alex in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 01-29-2005, 03:14 PM


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1