> Events in VBA Excel Macros

Excel VBATutorial                      Tutorial on Excel macros

 

Excel VBA Macros

Excel Macros Table of Contents

Excel Visual Basic Editor

Excel Macros Vocabulary

Excel Userforms

Excel Consulting

Excel Tutorial on Macros

Email to excel-vba.com

Here is a sample of what you will find in lesson 9 of the downloadable Tutorial on Excel macros

Lesson 9 on Excel macros (VBA):

Starting, Triggering a Macro in Excel 2007 to 2014 (The Events)

Note 1: If you are using Excel 1997 to 2006 see lesson 9 here

Note 2: Print this page, open Excel and a open a new workbook. Use ALT/F11 to open the Visual Basic Editor as you learned in lesson 1.

When does the VBA procedure (macro) start? When an EVENT happens. The event is what triggers the VBA Excel procedure. In earlier lessons you have used an event to start your macros. In the Visual Basic Editor you have gone to the menu bar and clicked on "Run/Run Sub/Userform" and the macro was executed. You have also clicked on the F8 key at the top of your keyboard and the macro got executed line by line.

You do not want your user to go to the Visual Basic Editor to trigger a macro. A lot of other events can happen to start a macro. The event that is mostly (85%) of macros used is clicking on a button. The button can be on the worksheet or on a userform that you would develop. The event can also be: opening the workbook, selecting a sheet, the value of a cell changing due to a manual input or due to the recalculation of a formula, clicking on a selected keystroke or going to the right menu item in Excel.

Preparing the Exercise on Events

To complete the following exercises, copy paste the code below from your browser to the code window of "Sheet1" of the new Excel workbook as you have learned in previous lessons.

Sub proFirst()

        Range("A1" ).Value = 34
        Range("A2" ).Value = 66
        Range("A3" ).Formula = "=A1+A2"

        Range("A1" ).Select

End Sub

Macros Triggered from the Developer Ribbon

Step 1: Select "Macros" from the "Developer" ribbon. You will see the "Macro" dialog window below.

VBA events

Step 2: "Sheet1.proFirst" being selected in the list box and its name appearing in the text box above the list box just click "Run".  The macro is automatically executed

Step 3: Erase the contents of cells A1, A2 and A3

You now see that colleagues must have installed VBA on their own computer to be able to use your macros from the "Developer" ribbon.

Macros Triggered by a Keystroke

In this second first exercise on events we will get the macro to be keyboard activated by capital "s" (Shift/S).  First you need to program a key. To do so:

Step 1: Select "Macros" from the "Developer" ribbon. You will see the "Macro" dialog window below.

VBA events

Step 2: "Sheet1.proFirst" being selected in the list box and its name appearing in the text box above the list box just click on "Options". A new dialog window "Macro Options" appears:

VBA events option

Step 3: In the shortcut key text box enter a capital "s" "SHIFT/s" and then click "OK". Click "Cancel" in the dialog window

Step 4: If you now click "CTRL/SHIFT/S" the macro will be executed instantly.

Macros Triggered by Clicking on a Text Box on the Worksheet

More than 90% of the macros are  triggered by a click on a button located on a worksheet.

We prefer using text boxes rather than VBA command buttons because they are much easier to maintain and allow much more creativity in the design. You can use the font that you like and the background color that fits your needs. If you are a little creative you can add 3D effects, special borders and others.

Step 1: From the "Insert ribbon" click on the "Text Box" icon once. Lower the curser toward the sheet, click and hold the left button of the mouse and stretch the text box to the desired dimension.

Step 2: Right click on the text box, select "Assign Macro" from the menu and the "Assign Macro" dialog window appears:

VBA event text box

Step 3: Select "Sheet1.proFirst" from the list box and its name appears in the text box above the list box just click on "OK".

Step 3: Click away from the text box on the Excel sheet.

Step 4: Left click on the text box and the macro is executed.

You can assign macros to text boxes, images or WordArt using the same approach.


We hope you have enjoyed this introduction to lesson 9
for more on this topic and a complete course on Excel macros download the
Tutorial on Excel Macros


Next Lesson: VBA Macros Security and Protection in Excel

 

Excel Tutorial on Macros

Excel VBA Macros

Excel Macros Table of Contents

Excel Visual Basic Editor

Excel Macros Vocabulary

Excel Userforms

Excel Consulting

The following is for Excel 1997 to 2006 users

Excel VBATutorial                             Tutorial on Excel macros

 

Excel VBA Macros

Excel Macros Table of Contents

Excel Visual Basic Editor

Excel Macros Vocabulary

Excel Userforms

Excel Consulting

Excel Tutorial on Macros

Here is a sample of what you will find in lesson 9 of the downloadable Tutorial on Excel macros

Lesson 9 on Excel macros (VBA):

Starting, Triggering  a Macro  in Excel  in Excel 1997 to 2006 (Events)

Note: If you are using Excel 2007 to 2011 see lesson 9 here

Note: Print this page, open Excel and a open a new workbook. Use ALT/F11 to open the Visual Basic Editor as you learned in lesson 1.

When does the VBA procedure (macro) start? When an EVENT happens. The event is what triggers the VBA Excel procedure. In earlier lessons you have used an event to start your macros. In the Visual Basic Editor you have gone to the menu bar and clicked on "Run/Run Sub/Userform" and the macro was executed. You have also clicked on the F8 key at the top of your keyboard and the macro got executed line by line.

You do not want your user to go to the Visual Basic Editor to trigger a macro. A lot of other events can happen to start a macro. The event that is mostly (90%) of macros used is clicking on a button. The button can be on the worksheet or on a userform that you would develop. The event can also be: opening the workbook, selecting a sheet, the value of a cell changing due to a manual input or due to the recalculation of a formula, clicking on a selected keystroke or going to the right menu item in Excel.

Preparing the Exercise on Events

To complete the following exercises, copy paste the code below from your browser to the code window of "Sheet1" of the new Excel workbook as you have learned in previous lessons.

Sub proFirst()

        Range("A1" ).Value = 34
        Range("A2" ).Value = 66
        Range("A3" ).Formula = "=A1+A2"

        Range("A1" ).Select

End Sub

Macros Triggered from the Excel Menu

Step 1: From the menu bar select "Macro/Macros". You will see the "Macro" dialog window below.

VBA events

Step 2: "Sheet1.proFirst" being selected in the list box and its name appearing in the text box above the list box just click "Run".  The macro is automatically executed

Step 3: Erase the contents of cells A1, A2 and A3.

Macros Triggered by a Keystroke

In this second first exercise on events we will get the macro to be keyboard activated by capital "s" (Shift/S).  First you need to program a key. To do so:

Step 1: From the menu bar select "Macro/Macros". You will see the "Macro" dialog window below.

VBA events

Step 2: "Sheet1.proFirst" being selected in the list box and its name appearing in the text box above the list box just click on "Options". A new dialog window "Macro Options" appears:

VBA events option

Step 3: In the shortcut key text box enter a capital "s" "SHIFT/s" and then click "OK". Click "Cancel" in the dialog window

Step 4: If you now click "CTRL/SHIFT/S" the macro will be executed instantly.

Macros Triggered by Clicking on a Text Box on the Worksheet

More than 90% of the macros are  triggered by a click on a button located on a worksheet.

We prefer using text boxes rather than VBA command buttons because they are much easier to maintain and allow much more creativity in the design. You can use the font that you like and the background color that fits your needs. If you are a little creative you can add 3D effects, special borders and others.

To work with text boxes you need to see the "Drawing" toolbar. If you do not see it on your screen go to "View/Toolbars" and select "Drawing":

Excel Drawing Toolbar

Step 1: On the "Drawing" toolbar click on the text box icon once. 

Step 2: Move the curser toward the sheet, click and hold the left button of the mouse and stretch the text box to the desired dimension.

Step 3: Right click on the text box, select "Assign Macro" from the menu and the "Assign Macro" dialog window appears:

VBA event text box

Step 4: Select "Sheet1.proFirst" from the list box and its name appears in the text box above the list box just click on "OK".

Step 5: Click away from the text box on the Excel sheet.

Step 6: Left click on the text box and the macro is executed.

You can assign macros to text boxes, images or WordArt using the same approach.


We hope you have enjoyed this introduction to lesson 9
for more on this topic and a complete course on Excel macros download the
Tutorial on Excel Macros


Next Lesson: VBA Macros Security and Protection in Excel

 

Excel Tutorial on Macros

Excel VBA Macros

Excel Macros Table of Contents

Excel Visual Basic Editor

Excel Macros Vocabulary

Excel Userforms

Excel Consulting