Share via


ListObject.Change Event (2007 System)

Occurs when something changes in the ListObject control.

Namespace:  Microsoft.Office.Tools.Excel
Assembly:  Microsoft.Office.Tools.Excel.v9.0 (in Microsoft.Office.Tools.Excel.v9.0.dll)

Syntax

'Declaration
Public Event Change As ListObjectChangeHandler
'Usage
Dim instance As ListObject 
Dim handler As ListObjectChangeHandler 

AddHandler instance.Change, handler
public event ListObjectChangeHandler Change
public:
 event ListObjectChangeHandler^ Change {
    void add (ListObjectChangeHandler^ value);
    void remove (ListObjectChangeHandler^ value);
}
JScript does not support events.

Remarks

This event is raised if the ListObject control is changed by either a programmatic modification or user interaction.

This event does not occur when cells change during a recalculation.

Examples

The following code example creates a ListObject and a Change event handler. To raise the Change event, add text to one of the cells in the ListObject and then press ENTER.

This version is for a document-level customization.

WithEvents ChangeList As Microsoft.Office.Tools.Excel.ListObject
Private Sub ListObject_Change()
    ChangeList = Me.Controls.AddListObject( _
        Me.Range("A1", "C4"), "ChangeList")
End Sub 


Sub List1_Change(ByVal targetRange As _
    Microsoft.Office.Interop.Excel.Range, _
    ByVal changedRanges As Microsoft.Office.Tools.Excel.ListRanges) _
    Handles ChangeList.Change

    Dim cellAddress As String = targetRange.Address( _
        ReferenceStyle:=Excel.XlReferenceStyle.xlA1)

    Select Case changedRanges
        Case Microsoft.Office.Tools.Excel.ListRanges.DataBodyRange
            MsgBox("The cells at range " & cellAddress & _
                " in the data body changed.")
        Case Microsoft.Office.Tools.Excel.ListRanges.HeaderRowRange
            MsgBox("The cells at range " & cellAddress & _
                " in the header row changed.")
        Case Microsoft.Office.Tools.Excel.ListRanges.TotalsRowRange
            MsgBox("The cells at range " & cellAddress & _
                " in the totals row changed.")
        Case Else
            MsgBox("The cells at range " & cellAddress & _
                " changed.")
    End Select 
End Sub
private void ListObject_Change()
{
    Microsoft.Office.Tools.Excel.ListObject list1 = 
        this.Controls.AddListObject(
        this.Range["A1", "C4"], "list1");
    list1.Change += new Microsoft.Office.Tools.Excel.
        ListObjectChangeHandler(list1_Change);
}

void list1_Change(Microsoft.Office.Interop.Excel.Range 
    targetRange, Microsoft.Office.Tools.Excel.ListRanges 
    changedRanges)
{
    string cellAddress = targetRange.get_Address(
        missing, missing, Excel.XlReferenceStyle.xlA1, 
        missing, missing);

    switch (changedRanges)
    {
        case Microsoft.Office.Tools.Excel.ListRanges.DataBodyRange:
            MessageBox.Show("The cells at range " + cellAddress +
                " in the data body changed.");
            break;
        case Microsoft.Office.Tools.Excel.ListRanges.HeaderRowRange:
            MessageBox.Show("The cells at range " + cellAddress +
                " in the header row changed.");
            break;
        case Microsoft.Office.Tools.Excel.ListRanges.TotalsRowRange:
            MessageBox.Show("The cells at range " + cellAddress +
                " in the totals row changed.");
            break;
        default:
            MessageBox.Show("The cells at range " + cellAddress +
                " changed.");
            break;
    }
}

This version is for an application-level add-in.

WithEvents ChangeList As ListObject
Private Sub ListObject_Change()
    Dim vstoWorksheet As Worksheet = CType( _
        Me.Application.ActiveWorkbook.Worksheets(1),  _
        Excel.Worksheet).GetVstoObject()
    ChangeList = vstoWorksheet.Controls.AddListObject( _
        vstoWorksheet.Range("A1", "C4"), "ChangeList")
End Sub 


Sub List1_Change(ByVal targetRange As  _
    Microsoft.Office.Interop.Excel.Range, _
    ByVal changedRanges As Microsoft.Office.Tools.Excel.ListRanges) _
    Handles ChangeList.Change

    Dim cellAddress As String = targetRange.Address( _
        ReferenceStyle:=Excel.XlReferenceStyle.xlA1)

    Select Case changedRanges
        Case ListRanges.DataBodyRange
            MsgBox("The cells at range " & cellAddress & _
                " in the data body changed.")
        Case ListRanges.HeaderRowRange
            MsgBox("The cells at range " & cellAddress & _
                " in the header row changed.")
        Case ListRanges.TotalsRowRange
            MsgBox("The cells at range " & cellAddress & _
                " in the totals row changed.")
        Case Else
            MsgBox("The cells at range " & cellAddress & _
                " changed.")
    End Select 
End Sub
private void ListObject_Change()
{
    Worksheet vstoWorksheet = ((Excel.Worksheet)
        this.Application.ActiveWorkbook.Worksheets[1]).GetVstoObject();
    ListObject list1 =
        vstoWorksheet.Controls.AddListObject(
        vstoWorksheet.Range["A1", "C4"], "list1");
    list1.Change += new ListObjectChangeHandler(list1_Change);
}

void list1_Change(Microsoft.Office.Interop.Excel.Range
    targetRange, Microsoft.Office.Tools.Excel.ListRanges
    changedRanges)
{
    string cellAddress = targetRange.get_Address(
        missing, missing, Excel.XlReferenceStyle.xlA1,
        missing, missing);

    switch (changedRanges)
    {
        case ListRanges.DataBodyRange:
            System.Windows.Forms.MessageBox.Show("The cells at range " + cellAddress +
                " in the data body changed.");
            break;
        case ListRanges.HeaderRowRange:
            System.Windows.Forms.MessageBox.Show("The cells at range " + cellAddress +
                " in the header row changed.");
            break;
        case ListRanges.TotalsRowRange:
            System.Windows.Forms.MessageBox.Show("The cells at range " + cellAddress +
                " in the totals row changed.");
            break;
        default:
            System.Windows.Forms.MessageBox.Show("The cells at range " + cellAddress +
                " changed.");
            break;
    }
}

.NET Framework Security

See Also

Reference

ListObject Class

ListObject Members

Microsoft.Office.Tools.Excel Namespace

Change History

Date

History

Reason

July 2008

Added a version of the code example for an application-level add-in.

SP1 feature change.