Trigger Happy In Excel VBA

You've spent hours writing or recording Macros that perform those repetitive and humdrum tasks in your spreadsheet. But have you given any thought on how your new VBA code will be triggered? How will the code be executed? Will the user initiate the code or some event will trigger the code to execute automatically? This article explores the options available in Excel 2007.

There are six methods to trigger VBA code:
1. Short cut Key.

2. Macro Dialog Box

3. Quick Access Toolbar Icon.

4. Worksheet Button or shape.

5. Custom Ribbon.

6. Event Procedure.

If you have recorded your Macro, you are asked to assign the keyboard shortcut combination when naming the Macro prior to recording. If you have written your VBA code directly into the Visual Basic Editor (VBE) you can assign a keyboard shortcut by clicking the Macros button in the 'Code' group on the Developer tab. Here, all Sub-procedures of Public scope are listed. Select the name of the procedure and click the 'Options' button. Enter the key combination for the selected procedure.

All Public Procedures listed in the Macros dialog box can be executed by selecting the procedure to execute and clicking 'Run'. Scope means if the procedure can be called from other procedures. All procedures stored in global modules are implicitly Public unless they begin with the words 'Private Sub', in which case, they will not show up in the Macro dialog box.

An alternative method to trigger a procedure is to add an icon on to the Quick Access Toolbar. Click the down-arrow to the right of the Quick Access Toolbar and select 'More Commands'. In the 'Choose commands from' box select 'Macros'. From the list of available procedures, select the procedure to add to the toolbar and click 'Add'. To change the appearance of the button, click the 'Modify' button. In the displayed dialog box, select an alternative icon and modify the procedure's display name. (Note: You should only add a macro to the Quick Access Toolbar which has been saved in the 'Personal Macro Workbook'.

You can also add a button or a shape in the body of the worksheet and assign a procedure to it. On the Developer tab, click the 'Insert' button in the 'Controls' group. Select the 'Button' shape and click in the worksheet. A button is added and the 'Assign Macro' dialog is shown. Select the required procedure to assign to the button and click 'OK'. To change the caption of the button, right click over the button and select 'Edit Text'. Alternatively, select a shape from the 'Shapes' icon in the 'illustrations' group on the Insert tab. Right click over the inserted shape and select 'Assign Macro'. Select the required procedure to assign to the button and click 'OK'.

In previous versions of Microsoft Excel, it was possible to create custom Toolbars and custom Menus and add macros to these objects. In Excel 2007 you can customise the Ribbon by adding custom tabs, groups and buttons.
This is done by inserting an XML part that defines the custom user interface (UI) into the Excel XML workbook. (The file extension for a Macro Enabled Workbook is xlsm). To do this requires some in-depth XML knowledge however with a bit of help from the 'Custom UI Editor Tool', it not as hard as it might seem.

1. Download and install the Custom UI Editor Tool from http://openxmldeveloper.org/attachment/808.ashx

2. Create /record the macros in a workbook saving it as a Macro Enabled workbook. Close the workbook

3. Run the newly installed Microsoft Office 2007 Custom UI Editor

4. From the File menu in the UI Editor, select 'Open' and navigate to the aforementioned workbook with your VBA code.

5. From the Sample menu in the UI Editor select 'Custom Tab'.

6. The basic XML is shown for one tab with the label 'Custom Tab', one group with the label 'Custom Group' and one button labelled 'Custom Button'. Each of these labels can be changed as required.

7. The argument imageMso="HappyFace" sets the image for the button. You can download a workbook from Microsoft which adds a group to the developer tab with icon galleries of built-in icons available for specifying the imageMso value. http://www.microsoft.com/downloads/details.aspx?familyid=12B99325-93E8-4ED4-8385-74D0F7661318 or search for 2007 Office System Add-In: Icons Gallery.

8. When the button is clicked, we will want a VBA procedure called 'WelcomeMessage' to run. For this to happen, modify the XML code to show onAction="WelcomeMessage".

9. To create the VBA needed to run when the button is clicked, select the 'Generate Callback's icon on the UI Editor's toolbar.

10. Copy all the VBA code shown.

11. Open the workbook again and paste the VBA code in a existing or new module in the VBA Editor.

12. Enter your VBA code between the Sub & End sub lines. (You may choose to call an existing sub routine.

13. In the workbook, you should now have a new tab, with a new group and a button.

An Event Procedure is a VBA procedure that is automatically executed in response to a specific event initiated by the user or the system. For example, every time the workbook is opened, closed, saved etc. The Event Procedures are stored in the module of the object where the event occurs. For example, the aforementioned events are all Workbook events and will be stored in the 'ThisWorkbook' module. Other events like Change, BeforeRightClick and calculate are stored in the relevant Sheet module (Sheet1, Sheet2 etc). Each event must also be named in a specific way.

If we want a VBA procedure to run when the workbook is opened:

1. In the VBA Editor, double click the 'ThisWorkbook' module to open it.

2. Select the Object drop down box at the top left of the code window (will show 'General' by default) and select Workbook.

3. From the Procedure dropdown box at the top right of the code window, select the event required, e.g. 'Open'. A new procedure called Workbook_Open is created and the VBA code for this event should be entered between the procedure name and the 'End Sub' statement

Conclusion: VBA procedures, written or recorded can be triggered by a wide range of methods by the user or automatically by the system and by other VBA code.