XL97: Using the SheetChange Event in Microsoft Excel 97Last reviewed: March 13, 1998Article ID: Q166347 |
The information in this article applies to:
Article idea submitted by: Derek Phoenix
SUMMARYIn Microsoft Excel 97, you can use the SheetChange event in Visual Basic for Applications to automatically run a macro whenever you change the contents of any worksheet in the workbook. This article explains how to use the SheetChange event and the SheetChange subroutine.
MORE INFORMATIONMicrosoft provides examples of Visual Basic for Applications procedures for illustration only, without warranty either expressed or implied, including, but not limited to the implied warranties of merchantability and/or fitness for a particular purpose. The Visual Basic procedures in this article are provided 'as is' and Microsoft does not guarantee that they can be used in all situations. While Microsoft support engineers can help explain the functionality of a particular macro, they will not modify these examples to provide added functionality, nor will they help you construct macros to meet your specific needs. If you have limited programming experience, you may want to consult one of the Microsoft Solution Providers. Solution Providers offer a wide range of fee-based services, including creating custom macros. For more information about Microsoft Solution Providers, call Microsoft Customer Information Service at (800) 426-9400.
How to Access the SheetChange Subroutine in the Visual Basic EditorEach workbook has a single SheetChange event. To view or edit the subroutine that uses the SheetChange event, use the following steps:
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As _ Excel.Range) '<code goes here> End SubThis is the SheetChange subroutine for the active workbook. Whenever the SheetChange event occurs, this subroutine is automatically run. NOTE: The first line of the subroutine (Private Sub... Excel.Range) should be entered on a single line without an underscore (_) character.
What Are "Sh" and "Target"?The SheetChange subroutine accepts two arguments. By default, these arguments are Sh and Target.
Compile error: Event procedure does not match description of event having the same name Using the SheetChange SubroutineThe SheetChange subroutine works like any other Visual Basic for Applications subroutine in Microsoft Excel. You can use the Sh and Target arguments to determine the worksheet and range of cells that changed, or to determine the value of the changed cells. You can use these values to perform other actions. For example, the following subroutine opens a workbook when you type the name into a cell in a worksheet in the active workbook:
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As _ Excel.Range) Workbooks.Open Filename:="C:\My Documents\" & Target.Value End SubBecause Target represents the changed cell, Target.Value returns the value in that cell. If the workbook for the name you typed exists in the My Documents folder on drive C, it is opened. This example subroutine only accepts values that are less than or equal to 100:
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As _ Excel.Range) If Target.Value > 100 Then MsgBox "This value is too high! Try again." Target.Clear Target.Select End If End SubIf you type a value that is greater than 100 in a cell in the workbook, a message box appears and the value is removed. The following example subroutine runs another macro if you type a value in cell A1 of Sheet1:
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As _ Excel.Range) If Sh.Name = "Sheet1" And Target.Address = "$A$1" Then RunOtherSub 'This is the name of the macro to run. End If End Sub Sub RunOtherSub() MsgBox "You typed a value in Sheet1!$A$1." End SubIf any other cell in the workbook is changed, the subroutine does nothing.
|
Additional query words: XL97
© 1998 Microsoft Corporation. All rights reserved. Terms of Use. |