Microsoft provides programming examples for illustration only, without warranty either expressed or implied. This includes, but is not limited to, the implied warranties of merchantability or fitness for a particular purpose. This article assumes that you are familiar with the programming language that is being demonstrated and with the tools that are used to create and to 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 requirements.
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:
- On the Tools menu, point to Macro, and click Visual Basic Editor.
- 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
- Double-click ThisWorkbook.
- In the Code window, click Workbook in the Object list. Then, click SheetChange in the Procedure list.
A subroutine that is similar to the following appears:
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As _
Excel.Range)
'<code goes here>
End Sub
This is the
SheetChange subroutine for the active workbook. Whenever the
SheetChange event occurs, this subroutine runs automatically.
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:
- Target represents the range of cells (or a single cell) that 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
Note that these arguments may be named differently; for example, you can
use
SheetName and
ChangedRange instead of
Sh and
Target. However, do not completely remove the arguments from the subroutine. If you do, you may receive the following error message:
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)
Workbooks.Open Filename:="C:\My Documents\" & Target.Value
End Sub
Because 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 Sub
If 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 Sub
If any other cell in the workbook is changed, the subroutine does nothing.