Events in Excel VBA

Workbook Open Event  |  Worksheet Change Event

Events are actions performed by users which trigger Excel VBA to execute code.

Workbook Open Event

Code added to the Workbook Open Event will be executed by Excel VBA when you open the workbook.

1. Open the Visual Basic Editor.

2. Double click on This Workbook in the Project Explorer.

3. Choose Workbook from the left drop-down list. Choose Open from the right drop-down list.

4. Add the following code line to the Workbook Open Event:

MsgBox “Good Morning”

5. Save, close and reopen the Excel file.

Result:

Worksheet Change Event

Code added to the Worksheet Change Event will be executed by Excel VBA when you change a cell on a worksheet.

1. Open the Visual Basic Editor.

2. Double click on a sheet (for example Sheet1) in the Project Explorer.

3. Choose Worksheet from the left drop-down list. Choose Change from the right drop-down list.

Add the following code lines to the Worksheet Change Event:

4. The Worksheet Change Event listens to all changes on Sheet1. We only want Excel VBA to do something if something changes in cell B2. To achieve this, add the following code lines:

If Target.Address = “$B$2” Then

End If

5. We only want Excel VBA to show a MsgBox if the user enters a value greater than 80. To achieve this, add the following code line between If and End If.

If Target.Value > 80 Then MsgBox “Goal Completed”

6. On Sheet1, enter a number greater than 80 into cell B2.

Result:

BeforeDoubleClick Event

Code added to the Worksheet BeforeDoubleClick Event will be executed by Excel VBA when you double click a cell on a worksheet.

1. Open the Visual Basic Editor.

2. Double click on a sheet (for example Sheet1) in the Project Explorer.

3. Choose Worksheet from the left drop-down list. Choose BeforeDoubleClick from the right drop-down list.

Note: the ‘_’ symbol is used to continue the statement on a new line (in order to show you the complete code).

Add the following code lines to the Worksheet BeforeDoubleClick Event:

4. The code line below colors the active cell red.

Target.Font.Color = vbRed

5. Set the Cancel argument to true if you don’t want the default double click action (cell edit mode) to occur.

Cancel = True

6. Test the program by double clicking a cell on Sheet1.

Result:

Highlight Active Cell

Below we will look at a program in Excel VBA that highlights the row and column of the Active Cell (selected cell). This program will amaze and impress your boss.

Situation:

Each time we change the Active Cell on Sheet1, a macro needs to be executed. You can achieve this by creating a Worksheet SelectionChange Event.

1. Open the Visual Basic Editor.

2. Double click on Sheet1 (Sheet1) in the Project Explorer.

3. Choose Worksheet from the left drop-down list. Choose SelectionChange from the right drop-down list.

Add the following code lines to the Worksheet SelectionChange Event:

4. We declare four variables of type Integer. One named rowNumberValue, one named columnNumberValue, one named i and one named j.

Dim rowNumberValue As Integer, columnNumberValue As Integer, i As Integer, j As Integer

5. First, we add the line which changes the background color of all cells to ‘No Fill’.

Cells.Interior.ColorIndex = 0

6. We initialize the variable rowNumberValue with the row number of the Active Cell and the variable columnNumberValue with the column number of the Active Cell.

rowNumberValue = ActiveCell.row
columnNumberValue = ActiveCell.column

7. We highlight the column blue. That is: all the cells with row number smaller or equal to rowNumberValue and column number equal to columnNumberValue

For i = 1 To rowNumberValue
Cells(i, columnNumberValue).Interior.ColorIndex = 37
Next i

Explanation: for this example (see picture above), rowNumberValue equals 12 and columnNumberValue equals 8. The code lines between For and Next will be executed twelve times. For i = 1, Excel VBA colors the cell at the intersection of row 1 and column 8. For i = 2, Excel VBA colors the cell at the intersection of row 2 and column 8, etc.

Note: instead of ColorIndex number 37 (blue), you can use any ColorIndex number.

8. In a similar way, we highlight the row blue. That is: all the cells with row number equal to rowNumberValue and column number smaller or equal to columnNumberValue.

For j = 1 To columnNumberValue
Cells(rowNumberValue, j).Interior.ColorIndex = 37
Next j

9. Now it’s time to test the program. Download the Excel file and see how the row and column of the Active Cell are highlighted each time you change the Active Cell.

Create a Footer Before Printing

Below we will look at a program in Excel VBA that creates a footer before printing a workbook.

Create a Workbook BeforePrint Event. Code added to the Workbook BeforePrint Event will be executed by Excel VBA before you print a workbook.

1. Open the Visual Basic Editor.

2. Double click on This Workbook in the Project Explorer.

3. Choose Workbook from the left drop-down list. Choose BeforePrint from the right drop-down list.

4. To create a footer, add the following code line to the Workbook BeforePrint Event:

ActiveSheet.PageSetup.LeftFooter = ActiveWorkbook.FullName

Note: The PageSetup object contains all page setup attributes (left footer, bottom margin, paper size, left margin, and so on) as properties.

5. Test the program by printing your workbook.

Result:

Bills and Coins

Below we will look at a program in Excel VBA that splits an amount of money into bills and coins.

Situation:

Create a Worksheet Change Event. Code added to the Worksheet Change Event will be executed by Excel VBA when you change a cell on a worksheet.

1. Open the Visual Basic Editor.

2. Double click on Sheet1 (Sheet1) in the Project Explorer.

3. Choose Worksheet from the left drop-down list. Choose Change from the right drop-down list.

Add the following code lines to the Worksheet Change Event:

4. Declare a variable called amount of type Double and a variable i of type Integer.

Dim amount As Double, i As Integer

5. The Worksheet Change Event listens to all changes on Sheet1. We only want Excel VBA to do something if something changes in cell B2. To achieve this, add the following code line:

If Target.Address = “$B$2” Then

6. We initialize the variable amount with the value of cell B2.

amount = Range(“B2”).Value

7. We empty the range with the frequencies.

Range(“B5:B16”).Value = “”

8. Now it’s time to split the entered amount of money. We start a For Next loop.

For i = 5 To 16

9. We will make use of the Do While Loop structure. Code placed between these words will be repeated as long as the part after Do While is true. We want Excel VBA to repeat the code lines at step 10 as long as amount is larger or equal to Cells(i,1).value.

Do While amount >= Cells(i, 1).Value

Loop

10. Add the following code lines to the Do While Loop.

Cells(i, 2).Value = Cells(i, 2).Value + 1
amount = amount – Cells(i, 1).Value

Explanation: as long as amount is larger or equal to Cells(i,1).value, the amount contains bills/coins of this value. As a result, Excel VBA increments the frequency of this bill/coin (first line) and subtracts the value of the bill/coin from amount (second line). This process will be repeated until amount becomes smaller than Cells(i,1).value. Next, Excel VBA increments i and goes to the next bill/coin to see how many times this bill/coin fits in the amount left. This way the amount of money will be split into bills and coins until there is no money left to split anymore.

11. Close the For Next loop and don’t forget to close the if statement (both outside the Do While Loop).

   Next i
End if

12. Test the program.

Result:

Note: of course the entered amount does not necessarily contains every bill/coin. If amount does not contain a certain bill/coin, the part after Do While never becomes true for this bill/coin and Excel VBA goes directly to the next bill/coin.

Rolling Average Table

Below we will look at a program in Excel VBA that creates a rolling average table. Place a command button on your worksheet and add the the following code line:

Range(“B3”).Value = WorksheetFunction.RandBetween(0, 100)

This code line enters a random number between 0 and 100 into cell B3. We want Excel VBA to take the new stock value and place it at the first position of the rolling average table. All other values should move down one place and the last value should be deleted.

Create a Worksheet Change Event. Code added to the Worksheet Change Event will be executed by Excel VBA when you change a cell on a worksheet.

1. Open the Visual Basic Editor.

2. Double click on Sheet1 (Sheet1) in the Project Explorer.

3. Choose Worksheet from the left drop-down list. Choose Change from the right drop-down list.

Add the following code lines to the Worksheet Change Event:

4. Declare a variable called newvalue of type Integer and two ranges (firstfourvalues and lastfourvalues).

Dim newvalue As Integer, firstfourvalues As Range, lastfourvalues As Range

5. The Worksheet Change Event listens to all changes on Sheet1. We only want Excel VBA to do something if something changes in cell B3. To achieve this, add the following code line:

If Target.Address = “$B$3” Then

6. We initialize newvalue with the value of cell B3, firstfourvalues with Range(“D3:D6”) and lastfourvalues with Range(“D4:D7”).

newvalue = Range(“B3”).Value
Set firstfourvalues = Range(“D3:D6”)
Set lastfourvalues = Range(“D4:D7”)

7. Now comes the simple trick. We want to update the rolling average table. You can achieve this by replacing the last four values with the first four values of the table and placing the new stock value at the first position.

lastfourvalues.Value = firstfourvalues.Value
Range(“D3”).Value = newvalue

8. Don’t forget to close the if statement.

End if

9. Finally, enter the formula =AVERAGE(D3:D7) into cell D8.

10. Test the program by clicking on the command button.

Leave a Reply

Your email address will not be published.