Additional Information:
This video shows you several ways of triggering macros so you can run your scripts without accessing the Visual Basic Editor. This allows you to automate processes and lets you share your work with less advanced Excel users.
The most obvious way to trigger a macro is to use a button, as demonstrated in the video. In Excel 2007 and 2010, you can find these buttons under Developer->Controls->Insert. You will note that there are two different types of button. This video deals with ActiveX controls.
ActiveX buttons can be assigned names and Visual Basic properties so integrate nicely. The alternative is to use a form object. These are essentially images. They can be linked to macros by right-clicking on them and selecting "Assign Macro". As the link is more superficial, it is more easily lost so this technique should be discouraged when creating simple buttons. However, the ability to assign macros to images gives you the ability to create aesthetically pleasing spreadsheets.
The video also demonstrates how to trigger macros based on user actions. The most useful tends to be the Worksheet Change event which is covered in the video. There is a related "Private Sub Worksheet_SelectionChange(ByVal target as Range)". This gets triggered each and every time a user selects a new cell. As a user may click anywhere in a sheet, these sub-routines can be started very easily. This makes it extremely useful, but its potency makes it a little harder to use.
Generally a macro can be assigned to any action a user might take e.g. selecting sheets, closing files, even right-clicking on a mouse. Whether you choose to use these requires careful consideration. An example of this would be the BeforeClose event which runs a macro when a user closes the file. The problem is that the changes made then need to be saved. If you let the user opt out of saving the file then you cannot guarantee the changes made have been retained. If you automatically save the changes, you are no longer letting the user close the file without saving which means they cannot close the file as a method for removing errors.
The video explains that you can stop macros starting other macros by disabling events. This means no further macros will start unless you click on a button or run code from within the Visual Basic Editor itself.
You may find you encounter errors in a sub-routine where you have disabled events at the start. If you click End or OK on the error message to exit the script, they will remain disabled. To re-enable them, you will need to create and run a new sub-routine including the line "Application.EnableEvents = True".
