Three Ways To Use VBA Event Programming In Excel


Capturing events in Excel can be a powerful tool in boosting the performance of your Excel application. It can be helpful to know what a user is doing in your spreadsheet to capture errors, or to create automated actions.

This article will explain how to set up event capture and three simple ways it can be of use.

Setting Up Event Capture

There are two main types of event capture.

  • Actions you would like to take when a workbook opens or closes
  • Capturing actions from a user such as data entry while the workbook is open

Event capture procedures must be specified and placed in the correct part of the VBA window. Instead of placing the procedure in a module, they are placed in the appropriate worksheet or workbook section of the code window.

For example, to activate an event on opening the workbook, the following code can be placed in the "thisWorkbook" section.

  Sub workbook_open () 

'code goes here
End Sub

If you want to capture events that occur in a specific worksheet, you place the code in the appropriate worksheet code window.

  Private Sub Worksheet_Change (ByVal Target As Range) 
End Sub

Examples Of Event Capture

Now you know how to set up event programming, here are three examples where event capture can be useful.

1. Activating A Specific Worksheet On Opening The File

When an Excel spreadsheet file is opened, your code can activate a particular sheet. With further enhancement, the procedure could find the next empty row in preparation for data entry.

  Sub workbook_open () 

worksheets ("mySheet")
End Sub

2. Capture User Errors

By using the worksheet change event, you can determine whether errors are being made during data entry. In this example, an error is generated if the user enters a non-numeric character.

  Private Sub Worksheet_Change (ByVal Target As Range) 

if not isNumeric (target) then msgbox ("Error")
End Sub

3. Actions When Closing The File

Although Excel will optionally save any changes when closing the spreadsheet, you can use the close event take other actions. For example, to record the last empty cell in worksheet one you can use this code:

  Private Sub Workbook_BeforeClose (Cancel As Boolean) 

Dim lastCell

Worksheets (1) .Activate

lastCell = Range ("a" & Rows.Count) .End (xlUp) .Address

Worksheets (2) .Activate

Range ("a1"). Value = lastCell
End Sub


Event programming in Excel enables you to customize your application by capturing user actions and specific events, saving you time and frustrating in your daily use of Excel spreadsheets.



Please enter your comment!
Please enter your name here