共用方式為

Running VBA Code When Events Occur in Excel 2010

  • 發行項

Summary:  Learn how to run Visual Basic for Applications (VBA) code in Microsoft Excel 2010 when events occur in a workbook, such as when a user changes the selection. Running VBA code from events enables you to perform tasks automatically without requiring the user to click shortcuts or run macros.

Applies to: Excel 2010 | Office 2010 | SharePoint Server 2010 | VBA

In this article
Understanding Event Handlers in VBA
Highlighting the Current Row and Column
Splitting Panes at the Current Row and Column
Adding a Comment When a Cell Value Changes
Creating a Custom Shortcut Menu
Starting a Timer with a Keyboard Shortcut
Using the Page Up and Page Down Keys to Page Through Worksheets
Using Events to Automatically Save Changes to a Workbook
Conclusion
Additional Resources

Published:  June 2011

Provided by:  Community Member Icon Dr. Gerard M. Verschuuren, Mr. Excel | Mark Roberts, Microsoft Corporation

Contents

  • Understanding Event Handlers in VBA

  • Highlighting the Current Row and Column

  • Splitting Panes at the Current Row and Column

  • Adding a Comment When a Cell Value Changes

  • Creating a Custom Shortcut Menu

  • Starting a Timer with a Keyboard Shortcut

  • Using the Page Up and Page Down Keys to Page Through Worksheets

  • Using Events to Automatically Save Changes to a Workbook

  • Conclusion

  • Additional Resources

Click to get code  Download sample workbook: Running VBA Code When Events Occur (Events.xlsm)

Understanding Event Handlers in VBA

By reading this article, you can learn how to perform tasks by running Visual Basic for Applications (VBA) code when something happens in a workbook, such as opening the workbook, making or changing the selection on a worksheet, right-clicking, or switching worksheets. The set of actions or occurrences that you can associate with VBA code are events, and the special kind of procedure that runs when the event occurs is an event handler.

The examples in this article are focused on events associated with the Worksheet and Workbook objects, as well as two methods of the Application object (the OnKey and OnTime methods) that function like events. To see lists of the events associated with Microsoft Excel objects, see the topic Worksheet Object Events in the Events, Worksheet Functions, and Shapes section of the Excel 2010 Developer Reference.

To run the code described in this article with sample data, download the Events sample workbook.

Highlighting the Current Row and Column

The Events sample workbook contains an event-handler procedure associated with the 1-Cross worksheet that automatically displays a horizontal and vertical marker that follows the user's selection on the sheet, as shown in Figure 1.

Figure 1. Highlighting the current row and column

Highlighting current row and column

To create this example, the event-handler code is associated with the SelectionChange event of the Worksheet object. As its name suggests, the SelectionChange event occurs whenever a user changes his or her selection on the worksheet. To add an event handler for a worksheet, use the following procedure.

To add an event handler for a worksheet event

  1. On the Developer tab, click Visual Basic.

  2. In Project Explorer, click the worksheet that you want to work with.

  3. In the left drop-down list above code editor, select Worksheet.

  4. In the right drop-down list above the code editor, select the event for which you want to create an event handler.

    For the following example, this is the SelectionChange event, which is the default selection from the drop-down list.

Note

If the Developer tab isn't visible on the ribbon of Excel, click File, click Options, click Customize Ribbon, and then select the Developer check box under Main Tabs.

Notice that the event handler for the SelectionChange event has an event argument named Target, which provides information about the range of cells that a user selected. However, because the Range object returned by the Target argument can consist of multiple cells, the code for this event handler uses the ActiveCell property of the Application object instead. The ActiveCell property returns a Range object that refers to only one cell (the current cell or the upper-left cell of a range of cells), which the event handler uses to highlight the row and column the user is in.

The code works by setting the ColorIndex property of the interior of the row and column that contains the selected cell to gray. (See the ColorIndex property topic for a list of the default color-index values.) Before doing so, the code uses the Cells property of the Worksheet object to clear the interior color for all cells in the worksheet.

The following listing shows the code for the SelectionChange event handler in the 1-Cross worksheet.

    ' Highlight the entire row and column for the ActiveCell.
    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
        Cells.Interior.ColorIndex = xlColorIndexNone
        ActiveCell.EntireRow.Interior.ColorIndex = 15
        ActiveCell.EntireColumn.Interior.ColorIndex = 15
    End Sub

Splitting Panes at the Current Row and Column

The 2-MoveButton worksheet in the Events workbook has a command button that follows your selection. Clicking the button toggles the screen split at that spot.

To create this feature, first add a button to the worksheet by clicking Insert on the Developer tab, and then clicking the Command Button icon in the ActiveX Controls palette. After you draw the button on the worksheet, right-click the button, and then click View Code. This action opens the code editor and creates a Click event handler for the button.

As shown in the following code excerpt, when a user clicks the button, the event-handler code for the ToggleSplit button uses an If/Else statement to check the value of the Split property, and turns the split on or off depending on the current value. Additionally, the code changes the Caption property of the button to reflect the action that occurs the next time the button is clicked.

    ' The button turns the window split on/off.
    Private Sub ToggleSplit_Click()
        If ActiveWindow.Split = True Then
            ActiveWindow.Split = False
            ToggleSplit.Caption = "Turn Split ON"
        Else
            ActiveWindow.Split = True
            ToggleSplit.Caption = "Turn Split OFF"
        End If
    End Sub

To make the button follow the user's selection requires an event handler for the SelectionChange event associated with the 2-MoveButton worksheet. As shown in the following code excerpt, the code sets the Top and Left coordinates of the button based on the values of the Top and Left coordinates of the current cell, offset by the values of the Height and Width properties of that cell.

    ' Make the button follow the user's navigation.
    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
        ToggleSplit.Top = ActiveCell.Top + ActiveCell.Height
        ToggleSplit.Left = ActiveCell.Left + ActiveCell.Width
    End Sub

Adding a Comment When a Cell Value Changes

The event-handler code associated with the 3-Comment worksheet tracks all changes the user makes to any cell on the sheet, and adds them to a comment that keeps expanding after each new change. To create this feature requires event handlers for two worksheet events:

  • The SelectionChange event to record the current value of the newly selected cell

  • The Change event to create a comment or add a new line to an existing comment if changes occurred

In the following event handlers, the variable OldValue must be scoped relative to the entire code module of the worksheet so that its value can be set in the first event handler, and then that value can be used in the second event handler. To do that, declare the variable at the top of the worksheet module above the procedures.

The following listing shows the code for the SelectionChange event-handler procedure that sets the value of the OldCellValue variable to the current value when the cell is selected.

    ' Record the current cell value.
    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
        OldCellValue = ActiveCell.Text
    End Sub

The Change event handler uses the Target argument this time, because the ActiveCell is not always the first cell of a selection. First, it uses the following line of code to ensure that Target argument references only one cell, and if so, exits the procedure because otherwise we would need to write much more code.

    If Target.Cells.Count > 1 Then Exit Sub

The next line of code creates the change-history item to add to the comment, which includes the time, the name of the user that made the change, and the previous value.

    NewComment = "Changed on " & Now() & " by " & Application.UserName & _
            " from " & OldCellValue

The next lines of code determine whether a comment is associated with the cell. If not, the code creates a new comment and then adds the newly created change-history item. Otherwise, it stores the text of the current comment in the OldComment variable, adds the new history item, and appends the old history items.

    If Target.Comment Is Nothing Then
        Target.AddComment NewComment
    Else
        OldComment = Target.Comment.Text
        Target.Comment.Text NewComment & vbLf & OldComment
    End If

The next line of code sizes the comment box to the widest line by setting the AutoSize property to True. The subsequent lines display the comment for five seconds. Because the Wait method suspends Excel from other activities until the wait period is over, the DoEvents function is called so that the operating system can process other actions.

    Target.Comment.Shape.TextFrame.AutoSize = True
    
    Target.Comment.Visible = True
    DoEvents
    Application.Wait Now + TimeValue("00:00:05")
    Target.Comment.Visible = False

The final lines of code ask the user whether to delete the change-history comments and, if yes, loops through all cells in the current region to delete comments.

    If MsgBox("Delete all comments?", vbYesNo) = vbYes Then
        For Each objCell In ActiveCell.CurrentRegion.Cells
            If Not objCell.Comment Is Nothing Then objCell.Comment.Delete
        Next objCell
    End If

The following listing shows the code for the entire Change event-handler procedure associated with the 3-Comment worksheet.

    ' Add a comment for each change.
    Private Sub Worksheet_Change(ByVal Target As Range)
        Dim OldComment As String, NewComment As String, objCell As Range
        
        If Target.Cells.Count > 1 Then Exit Sub
        
        NewComment = "Changed on " & Now() & " by " & Application.UserName & _
            " from " & OldCellValue
        
        If Target.Comment Is Nothing Then
            Target.AddComment NewComment
        Else
            OldComment = Target.Comment.Text
            Target.Comment.Text NewComment & vbLf & OldComment
        End If
        
        Target.Comment.Shape.TextFrame.AutoSize = True
        
        Target.Comment.Visible = True
        DoEvents
        Application.Wait Now + TimeValue("00:00:05")
        Target.Comment.Visible = False
        
        If MsgBox("Delete all comments?", vbYesNo) = vbYes Then
            For Each objCell In ActiveCell.CurrentRegion.Cells
                If Not objCell.Comment Is Nothing Then objCell.Comment.Delete
            Next objCell
        End If
    End Sub

Note

The procedure still has a couple potential drawbacks if you want to record the history of every change to the cell. The SelectionChange event handler does not run under the following circumstances:

  • If the user opens the file, and then changes the currently active cell. (The value of OldComment remains "" [an empty string], so no history item is recorded.)

  • If the user changes a cell, and then stays in it (for example, by pressing Ctrl+Enter), and then changes the cell again.

Creating a Custom Shortcut Menu

The example associated with the 5-PopupMenu worksheet of the Events workbook shows how to create a right-click shortcut menu that contains three commands. The code is contained in the worksheet BeforeRightClick event handler of the worksheet, plus three functions called from a code module to define the commands in the shortcut menu.

The first two lines of code create arrays that are used to create the three commands on the shortcut menu. The varCaption array contains the captions to display, and the varAction array contains the names of the Sub procedures to call for each command.

    varCaption = Array("Toggle Gridlines", "Toggle Formulas", "Print Preview")
    varAction = Array("Gridlines", "Formulas", "Preview")

The next line of code adds a new CommandBar object configured as a shortcut menu to the CommandBars collection, and sets the objMenu variable to that object.

    Set objMenu = CommandBars.Add(Position:=msoBarPopup, Temporary:=True)

The following lines of code loop through the two varCaption and varAction arrays to populate the commands contained in the shortcut menu.

    For i = 0 To UBound(varAction)
        Set objCommand = objMenu.Controls.Add
        objCommand.Caption = varCaption(i)
        objCommand.OnAction = varAction(i)
    Next i

The last two lines of code display the shortcut menu and then set the Cancel event argument of the BeforeRightClick event handler to True to prevent Excel from displaying its built-in shortcut menu.

    objMenu.ShowPopup
    Cancel = True

The following listing shows the code for the three procedures in Module1 that are called from the shortcut menu defined in the BeforeRightClick event-handler procedure. The Gridlines and Formulas procedures simply toggle the DisplayGridlines and DisplayFormulas properties, and the Preview procedure displays the worksheet in print preview.

    Sub Gridlines()
        ActiveWindow.DisplayGridlines = Not ActiveWindow.DisplayGridlines
    End Sub
    
    Sub Formulas()
        ActiveWindow.DisplayFormulas = Not ActiveWindow.DisplayFormulas
    End Sub
    
    Sub Preview()
        ActiveSheet.PrintPreview
    End Sub

The following code shows the entire listing for the BeforeRightClick event-handler procedure that is associated with the 5-PopupMenu worksheet.

    Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)
        Dim varCaption As Variant, varAction As Variant, i As Integer
        Dim objMenu As CommandBar, objCommand As CommandBarControl
        
        ' Create array of shortcut menu captions.
        varCaption = Array("Toggle Gridlines", "Toggle Formulas", "Print Preview")
        
        ' Create array of the Sub procedure names to call from shortcut menu.
        varAction = Array("Gridlines", "Formulas", "Preview")
        
        ' Add shortcut menu to CommandBars collection.
        Set objMenu = CommandBars.Add(Position:=msoBarPopup, Temporary:=True)
        
        ' Loop through arrays to add commands to shortcut menu.
        For i = 0 To UBound(varAction)
            Set objCommand = objMenu.Controls.Add
            objCommand.Caption = varCaption(i)
            objCommand.OnAction = varAction(i)
        Next i
        
        ' Display shortcut menu.
        objMenu.ShowPopup
        
        ' Cancel display of the built-in shortcut menu.
        Cancel = True
    End Sub

Starting a Timer with a Keyboard Shortcut

The example in this section shows how to use the OnKey method of the Application object, which functions like an event to run a procedure when a key or key combination is pressed. The procedure that is called by the OnKey method in this example uses the OnTime method of the Application object to trigger an alarm after a specified time period.

The code that defines the key to press and the procedure to call is a single line in the Open event handler for the workbook. The call to the OnKey method in following code excerpt specifies that pressing the End key calls the SetAlarm procedure that defines the alarm behavior.

    Private Sub Workbook_Open()
        Application.OnKey Key:="{END}", Procedure:="SetAlarm"
    End Sub

Note

After you put this line of code in the Open event handler of the workbook, you must close and reopen the workbook.

The following listing shows the SetAlarm procedure in Module1 that is called when the user presses the End key. The procedure prompts the user to specify the setting for the alarm, and then calls the OnTime method with the value specified by the user to determine when to call the ShowTime procedure to display the time.

    Sub SetAlarm()
        Dim strAlarm As String
        strAlarm = InputBox(Prompt:="At what time? (24-hour clock)", Title:="Set Alarm")
        If strAlarm = "" Then Exit Sub
        Application.OnTime EarliestTime:=TimeValue(strAlarm), Procedure:="ShowTime"
    End Sub

Note

  • The OnTime method does not block the system like the Wait method does, so no call to the DoEvents function is required.

  • As written in this example, the call to the OnTime method runs the ShowTime procedure at the time of day (24-hour clock) specified by the user. To create a timer that uses the amount of time elapsed from now, change the line to read as follows:

    Application.OnTime EarliestTime:=Now + TimeValue(strAlarm), Procedure:="ShowTime"

As shown in the following listing, the code for the ShowTime procedure displays the current time, and then calls the SetAlarm procedure again until the user cancels the InputBox prompt.

    Sub ShowTime()
        MsgBox Now
        SetAlarm
    End Sub

Using the Page Up and Page Down Keys to Page Through Worksheets

The example in this section uses the OnKey method to make the Page Up and Page Down keys move through the worksheets in a workbook.

As in the previous example, the calls to the OnKey method are located in the Open event handler of the workbook, as shown in the following code.

    Private Sub Workbook_Open()
        Application.OnKey Key:="{PgUp}", Procedure:="SheetsUp"
        Application.OnKey Key:="{PgDn}", Procedure:="SheetsDown"
    End Sub

The following code shows the listing for the SheetsUp and SheetsDown functions in Module1 called from the OnKey method calls in the Open event-handler procedure of the workbook. The code for the SheetsUp procedure increments the value of the index for the active worksheet by one, and then checks whether that value exceeds the number of worksheets; if not, the procedure advances to the next worksheet. The code for the SheetsDown procedure does the inverse.

    Sub SheetsUp()
        Dim i As Long
        i = ActiveSheet.Index + 1
        If i <= Sheets.Count Then Sheets(i).Select
    End Sub
    
    Sub SheetsDown()
        Dim i As Long
        i = ActiveSheet.Index - 1
        If i >= 1 Then Sheets(i).Select
    End Sub

Note

Because the Workbook_SheetDeactivate event handler described in the next section is in place in the sample workbook, you are also prompted to save the workbook as you page through worksheets.

Using Events to Automatically Save Changes to a Workbook

To use code to save the workbook automatically after a given interval, you can use a call to the OnTime method of the Application object in the Open event-handler procedure of the workbook.

The line of code in the event-handler procedure for the Open event of the Events sample workbook calls a procedure named SaveWB 10 minutes after the workbook was opened.

Private Sub Workbook_Open()
    Application.OnTime EarliestTime:=Now + TimeValue("00:10:00"), Procedure:="SaveWB"
End Sub

As shown in the following code, the first line of code in the SaveWB procedure displays a prompt to the user. If the user responds "Yes," the procedure calls the Save method. The second line of code repeats the same call to the OnTime method as the Open event-handler procedure to create a recursive operation that restarts the timer.

    Sub SaveWB()
        If MsgBox("Save workbook?", vbYesNo) = vbYes Then ActiveWorkbook.Save
        Application.OnTime EarliestTime:=Now + TimeValue("00:10:00"), Procedure:="SaveWB"
    End Sub

As shown in the following code, you can also prompt the user whether to save the workbook when he or she switches to another worksheet by calling a procedure such as SaveWB from an event-handler procedure for the SheetDeactivate event.

    Private Sub Workbook_SheetDeactivate(ByVal Sh As Object)
        SaveWB
    End Sub

Conclusion

This article and the Events sample workbook provide examples of how to run VBA code when events occur in a workbook, such as when a user changes the selection or changes the value in a cell.

Additional Resources