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.