XL98: Using the SheetChange Event in Microsoft Excel 98

• Microsoft Excel 98 Macintosh Edition

Article idea submitted by: Derek Phoenix

SUMMARY

In Microsoft Excel 98 Macintosh Edition, 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 INFORMATION

Microsoft provides programming examples 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. This article assumes that you are familiar with the programming language being demonstrated and the tools used to create and debug procedures. Microsoft support engineers can help explain the functionality of a particular procedure, but they will not modify these examples to provide added functionality or construct procedures to meet your specific needs. If you have limited programming experience, you may want to contact the Microsoft fee-based consulting line at (800) 936-5200. For more information about the support options available from Microsoft, please see the following page on the World Wide Web:

   http://www.microsoft.com/support/supportnet/refguide/default.asp

How to Access the SheetChange Subroutine in the Visual Basic Editor

Each workbook has a single SheetChange event. To view or edit the subroutine that uses the SheetChange event, follow these steps:

1. On the Tools menu, point to Macro, and click Visual Basic Editor.

2. If the Project window is not visible, click Project Explorer on the View menu.

   In the list of workbooks in the Project window, a list that is similar to the following appears:

         VBAProject (Book1)
            Microsoft Excel Objects
               Sheet1 (Sheet1)
               Sheet2 (Sheet2)
               Sheet3 (Sheet3)
               ThisWorkbook
   
   

3. Double-click ThisWorkbook.

4. In the Code window, click Workbook in the Object list. Then, click SheetChange in the Procedure list.

   Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As _
       Excel.Range)

       '<code goes here>

   End Sub

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.

• Sh represents the name of the worksheet that is changed. It signals the SheetChange subroutine to run. You can use this argument to determine the worksheet name, for example:

        xWorksheetName = Sh.Name
  

• Target represents the range of cells (or a single cell) that is changed. It signals the SheetChange subroutine to run. You can use this argument to determine the range of cells that changed, for example:

        xChangedRange = Target.Address
  

   Compile error:
   Event procedure does not match description of event having the same
   name

Using the SheetChange Subroutine

The 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)

       ' Substitute the name of your hard drive for "Macintosh HD".
       Workbooks.Open Filename:="Macintosh HD:Desktop Folder" & _
                                 Target.Value

   End Sub

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 Sub

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 Sub

For more information about the SheetChange event, click the Office Assistant in the Visual Basic Editor, type "sheetchange," click Search, and then click to view "SheetChange Event."

NOTE: If the Assistant is hidden, click the Office Assistant button on the Standard toolbar. If Microsoft Help is not installed on your computer, please see the following article in the Microsoft Knowledge Base:

   ARTICLE-ID: Q179216
   TITLE     : OFF98: How to Use the Microsoft Office Installer Program