Program by Levels

Tutorial on VBA for Excel (Macros)

bringing you the simplest and most efficient ways
to do things in VBA for Excel

VBA Lesson 10 of 33: Starting a Macro in Excel
(Levels: Beginners, Intermediate, Advanced)

When does the VBA procedure (macro) start? When an EVENT happens. The event is what triggers the VBA Excel procedure.

Clicking on a text box on the worksheet

95% of the VBA procedures that you develop are  triggered by a click on a text box located on a worksheet.

I 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 the likes.

A few note on Excel text boxes:

I always keep the Excel drawing toolbar visible at the bottom of my screen

You create text boxes by a left click on  the icon , let the button go, then go to the worksheet left click, hold and stretch the text box. When the border of the active text box is made of diagonal lines you can work the text inside the text box. If you click again on the border it becomes a set of dots and you then can work the text box itself. Right click on the border in any of the two states and you will see that the menus are  different.

First you  develop a macro in a module in the VBE. Then you click ob the text box and when the border becomes a set of dots right click on it and select "Assign a macro" . Select a macro from  the list that is offered to you. A simple macro to call a userform would look like this:

Sub proUserFormWeighing()
                frmWeighing.Show
End Sub

You can assign a VBA macro to a text box and also to a WordArt, a picture or any other shape from the "Drawing" toolbar.

Once a button (image, word art or text box) has been assigned a macro or an hyperlink you need to select it with a right click to modify it.

Download one of these buttons (right click on it in your browser and choose "Save image as"). Save it on your desktop:

Insert the  image that you have imported on the first sheet "Insert/Picture/From File/Desktop/..........gif" . Once the image has been added to the sheet, right click on the image, select "Assign Macro" and select a macro from the list. Click "OK" .

Now click on the image.

You can "borrow" all kinds of buttons from the Internet or create your own from the "Design" toolbar and use them as triggers for your VBA procedures.

From the Excel Menu

In Excel you can run an Excel VBA macro  by going to the menu "Tool/Macro/Macros.." then select the  macro from the list and click "Run" .

Open many workbooks with macros in them. When you go to the menu "Tool/Macro/Macros.."   you will notice that you have access to all the macros from all the open workbooks. This means that you can store ALL your useful Excel macros in a single workbook (call it myMacros.xls) and have access to them while the workbook is opened. Let's say for example that you have designed a macro that multiplies the content of a cell by 2. If "myMacros.xls" is open you can call this Excel macro  from any cell in any other workbook that is open. No need to copy your essential macros in all your workbooks just open myMacros.xls and put them to work.

Clicking on a Key of your Keyboard

First you need to program a key. To do so go to "Tool/Macro/Macros.." then select a  macro from the list. Click on "Options" and follow the instructions. A suggestion, assign your macros  to upper case keys ("Shift/A" instead of "Shift/a for example) to make sure that you don't use one of the many lower case keys that are already used by Excel. You can also assign a macro to a key when you use the Excel Macro recorder.

For Intermediate and Advanced Users

Events related to the worksheet or the cell

In the VB editor double click on "ThisWorbook" in the project window. At the top of the code window select "Workbook" in the left drop-down list and in the right one you will see 28 different events. Choose one and develop your procedure. The event that is used most of the time is the "Open" event. If for example you want your workbook to open on the sheet "Introduction" cell "A1" the code will be:

Private Sub Workbook_Open()
        Sheets("Introduction").select
        Range("A1").select
End Sub

Events related to the worksheet

In Excel right click on the tab of any sheet and chose "View Code" or double click on the name of any sheet in the project window of the VBE.   At the top of the code window select "Worksheet" in the left drop-down list and in the right one you will see 9  different events. Choose one and develop your procedure. Remember that any procedure that you develop under the event "Activate" or any other will run EACH time that the event happens. Developing procedures triggered by events of the worksheet is tricky and I don't personal develop too many. There are 2 events under worksheet that are really events related to cells: Change and Calculate.

Events related to the cell

There are not really an event related to the cell but two events related to the sheet can be use to trigger macros when something happens in a cell. The events are "Change" and "Calculate" .

Private Sub Worksheet_Change(ByVal target As Range)
    If target.Address = "$A$1" Then MsgBox "OK"
    If target.Column = "1" Then MsgBox "OK"
    If target.Row = "1" Then MsgBox "OK"
    If target.Column = "1" Or target.Column = "2" Then MsgBox "OK"
    If target.Value = "1" Then MsgBox "OK"
End Sub

A message box saying "OK will appear when you enter any value in cell A1 (first "If"), when you enter any value in the first column (second "If"), when you enter any value on the first row (third "If"), when you enter any value in columns 1 or 2 (forth "If") and if you enter 1 in any cell (fifth "If").

Private Sub Worksheet_Calculate()
        If Range("A1").Value > 100 Then MsgBox "OK"
End Sub

Enter a formula in cell "A1" (like "=C5"). When the result of the formula in "A1" is higher than 100 a message box says "OK" . Try entering 99 or 500 in cell "C5" .

Other Events

See lessons 3-1 and 3-10 for VBA events related to userforms and controls

This page is one of the 33 lessons from the
Tutorial on VBA for Excel (Macros)
VBA for Excel made simple