Excel VBA (Visual Basic for Applications) empowers you to take control of your spreadsheets by automating various tasks. One of the key features of Excel VBA is its ability to respond to events, which are user actions or occurrences in the Excel environment. In this tutorial, we’ll explore several essential Excel VBA events and how you can use them to streamline your work. Let’s dive in.

1. Workbook Open Event

The Workbook Open Event allows you to execute code when you open a workbook. To use this event:

• Open the Visual Basic Editor.
• Double-click on “ThisWorkbook” in the Project Explorer.
• Choose “Workbook” from the left drop-down list.
• Choose “Open” from the right drop-down list.

Add the following code to the Workbook Open Event:

MsgBox "Good Morning"

Save, close, and then reopen the Excel file. You’ll see a “Good Morning” message.

2. Worksheet Change Event

The Worksheet Change Event triggers when you change a cell in a worksheet. To set it up:

• Open the Visual Basic Editor.
• Double-click on a sheet (e.g., “Sheet1”) in the Project Explorer.
• Choose “Worksheet” from the left drop-down list.
• Choose “Change” from the right drop-down list.

Here’s an example of how to react to changes in cell B2:

If Target.Address = "$B$2" Then
    If Target.Value > 80 Then
        MsgBox "Goal Completed"
    End If
End If

When you enter a value greater than 80 in cell B2, a message will appear.

3. BeforeDoubleClick Event

This event runs when you double-click a cell. Follow these steps:

• Open the Visual Basic Editor.
• Double-click on a sheet (e.g., “Sheet1”) in the Project Explorer.
• Choose “Worksheet” from the left drop-down list.
• Select “BeforeDoubleClick” from the right drop-down list.

Use this code to change the font color and cancel the default double-click action (cell edit mode):

Target.Font.Color = vbRed
Cancel = True

When you double-click a cell, it will turn red, and you won’t enter edit mode.

4. Highlight Active Cell

This feature highlights the row and column of the active cell. Implement it as follows:

• Open the Visual Basic Editor.
• Double-click on the sheet (e.g., “Sheet1”) in the Project Explorer.
• Choose “Worksheet” from the left drop-down list.
• Select “SelectionChange” from the right drop-down list.

Add this code:

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

Cells.Interior.ColorIndex = 0

rowNumberValue = ActiveCell.Row
columnNumberValue = ActiveCell.Column

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

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

Now, each time you change the active cell on Sheet1, the corresponding row and column will turn blue.

5. Create a Footer Before Printing

You can use this event to add a footer before printing your workbook. Follow these steps:

• Open the Visual Basic Editor.
• Double-click on “ThisWorkbook” in the Project Explorer.
• Choose “Workbook” from the left drop-down list.
• Choose “BeforePrint” from the right drop-down list.

Use this code to create a left footer with the workbook’s full name:

ActiveSheet.PageSetup.LeftFooter = ActiveWorkbook.FullName

This will set the left footer of the printed document to the full name of your workbook.

6. Bills and Coins

This event helps you split an amount of money into bills and coins. Configure it this way:

• Open the Visual Basic Editor.
• Double-click on the sheet (e.g., “Sheet1”) in the Project Explorer.
• Choose “Worksheet” from the left drop-down list.
• Select “Change” from the right drop-down list.

Insert this code to handle the amount splitting:

Dim amount As Double, i As Integer

If Target.Address = "$B$2" Then
    amount = Range("B2").Value
    Range("B5:B16").Value = ""
    
    For i = 5 To 16
        Do While amount >= Cells(i, 1).Value
            Cells(i, 2).Value = Cells(i, 2).Value + 1
            amount = amount - Cells(i, 1).Value
        Loop
    Next i
End If

Now, when you change the value in cell B2 on Sheet1, Excel VBA will automatically split it into bills and coins.

7. Rolling Average Table

Create a rolling average table that updates with a new value. Place a command button on your worksheet and add the following code to generate random numbers and update the rolling average:

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

Then, configure the Worksheet Change Event:

• Open the Visual Basic Editor.
• Double-click on “Sheet1” in the Project Explorer.
• Choose “Worksheet” from the left drop-down list.
• Select “Change” from the right drop-down list.

Dim newValue As Integer, firstFourValues As Range, lastFourValues As Range

If Target.Address = "$B$3" Then
    newValue = Range("B3").Value
    Set firstFourValues = Range("D3:D6")
    Set lastFourValues = Range("D4:D7")

    lastFourValues.Value = firstFourValues.Value
    Range("D3").Value = newValue
End If

Now, every time you change the value in cell B3, the rolling average table updates accordingly.

These Excel VBA events are powerful tools for automating your spreadsheets and improving your efficiency. By responding to user actions and workbook events, you can create dynamic and responsive Excel applications that save time and reduce errors in your work.