ListObject.BeforeAddDataBoundRow Event
Occurs just before an attempt to add a new row to a ListObject control that is bound to data.
Namespace: Microsoft.Office.Tools.Excel
Assembly: Microsoft.Office.Tools.Excel (in Microsoft.Office.Tools.Excel.dll)
Syntax
'Declaration
Event BeforeAddDataBoundRow As BeforeAddDataBoundRowEventHandler
event BeforeAddDataBoundRowEventHandler BeforeAddDataBoundRow
Remarks
This event is raised only if the ListObject control is bound to data.
This event is raised only when a new row is added through the Microsoft Office Excel user interface. It is not raised when a new row is added programmatically.
Handle this event to perform additional validation, or to cancel addition of a row.
Examples
The following code example creates a DataTable and a ListObject, and binds the ListObject to the DataTable. It then creates a BeforeAddDataBoundRow event handler. To test the event, manually add a new row to the ListObject on sheet 1. The event handler removes the row and displays a message.
This version is for a document-level customization.
WithEvents BeforeAddDataBoundRowList As _
Microsoft.Office.Tools.Excel.ListObject
Private Sub ListObject_BeforeAddDataBoundRow()
' Create a new DataSet and DataTable.
Dim ds As New DataSet()
Dim dt As DataTable = ds.Tables.Add("Customers")
dt.Columns.Add(New DataColumn("LastName"))
dt.Columns.Add(New DataColumn("FirstName"))
' Add a new row to the DataTable.
Dim dr As DataRow = dt.NewRow()
dr("LastName") = "Chan"
dr("FirstName") = "Gareth"
dt.Rows.Add(dr)
' Create a list object.
BeforeAddDataBoundRowList = _
Me.Controls.AddListObject(Me.Range("A1"), _
"BeforeAddDataBoundRowList")
' Bind the list object to the DataTable.
BeforeAddDataBoundRowList.AutoSetDataBoundColumnHeaders = True
BeforeAddDataBoundRowList.SetDataBinding(ds, "Customers", _
"LastName", "FirstName")
End Sub
Private Sub List1_BeforeAddDataBoundRow(ByVal sender As Object, _
ByVal e As Microsoft.Office.Tools.Excel.BeforeAddDataBoundRowEventArgs) _
Handles BeforeAddDataBoundRowList.BeforeAddDataBoundRow
e.Cancel = True
MessageBox.Show("This data is read-only.")
End Sub
private void ListObject_BeforeAddDataBoundRow()
{
// Create a new DataSet and DataTable.
DataSet ds = new DataSet();
DataTable dt = ds.Tables.Add("Customers");
dt.Columns.Add(new DataColumn("LastName"));
dt.Columns.Add(new DataColumn("FirstName"));
// Add a new row to the DataTable.
DataRow dr = dt.NewRow();
dr["LastName"] = "Chan";
dr["FirstName"] = "Gareth";
dt.Rows.Add(dr);
// Create a list object.
Microsoft.Office.Tools.Excel.ListObject list1 =
this.Controls.AddListObject(
this.Range["A1"], "list1");
// Bind the list object to the DataTable.
list1.AutoSetDataBoundColumnHeaders = true;
list1.SetDataBinding(ds, "Customers", "LastName",
"FirstName");
// Create the event handler.
list1.BeforeAddDataBoundRow += new
Microsoft.Office.Tools.Excel.
BeforeAddDataBoundRowEventHandler(
list1_BeforeAddDataBoundRow);
}
void list1_BeforeAddDataBoundRow(object sender,
Microsoft.Office.Tools.Excel.BeforeAddDataBoundRowEventArgs e)
{
e.Cancel = true;
MessageBox.Show("This data is read-only.");
}
This version is for an application-level add-in. To use this code example, add the using System.Data; directive if you are using C# or the Imports System.Data statement if you are using Visual Basic.
WithEvents BeforeAddDataBoundRowList As ListObject
Private Sub ListObject_BeforeAddDataBoundRow()
' Create a new DataSet and DataTable.
Dim ds As New DataSet()
Dim dt As DataTable = ds.Tables.Add("Customers")
dt.Columns.Add(New DataColumn("LastName"))
dt.Columns.Add(New DataColumn("FirstName"))
' Add a new row to the DataTable.
Dim dr As DataRow = dt.NewRow()
dr("LastName") = "Chan"
dr("FirstName") = "Gareth"
dt.Rows.Add(dr)
' Create a list object.
Dim NativeWorksheet As Microsoft.Office.Interop.Excel.Worksheet =
Me.Application.Worksheets(1)
Dim vstoWorksheet As Microsoft.Office.Tools.Excel.Worksheet =
Globals.Factory.GetVstoObject(NativeWorksheet)
BeforeAddDataBoundRowList = _
vstoWorksheet.Controls.AddListObject( _
vstoWorksheet.Range("A1"), _
"BeforeAddDataBoundRowList")
' Bind the list object to the DataTable.
BeforeAddDataBoundRowList.AutoSetDataBoundColumnHeaders = True
BeforeAddDataBoundRowList.SetDataBinding(ds, "Customers", _
"LastName", "FirstName")
End Sub
Private Sub List1_BeforeAddDataBoundRow(ByVal sender As Object, _
ByVal e As Microsoft.Office.Tools.Excel.BeforeAddDataBoundRowEventArgs) _
Handles BeforeAddDataBoundRowList.BeforeAddDataBoundRow
e.Cancel = True
System.Windows.Forms.MessageBox.Show("This data is read-only.")
End Sub
private void ListObject_BeforeAddDataBoundRow()
{
// Create a new DataSet and DataTable.
DataSet ds = new DataSet();
DataTable dt = ds.Tables.Add("Customers");
dt.Columns.Add(new DataColumn("LastName"));
dt.Columns.Add(new DataColumn("FirstName"));
// Add a new row to the DataTable.
DataRow dr = dt.NewRow();
dr["LastName"] = "Chan";
dr["FirstName"] = "Gareth";
dt.Rows.Add(dr);
// Create a list object.
Worksheet vstoWorksheet =
Globals.Factory.GetVstoObject(this.Application.ActiveWorkbook.Worksheets[1]);
ListObject list1 =
vstoWorksheet.Controls.AddListObject(
vstoWorksheet.Range["A1"], "list1");
// Bind the list object to the DataTable.
list1.AutoSetDataBoundColumnHeaders = true;
list1.SetDataBinding(ds, "Customers", "LastName",
"FirstName");
// Create the event handler.
list1.BeforeAddDataBoundRow += new
BeforeAddDataBoundRowEventHandler(
list1_BeforeAddDataBoundRow);
}
void list1_BeforeAddDataBoundRow(object sender,
Microsoft.Office.Tools.Excel.BeforeAddDataBoundRowEventArgs e)
{
e.Cancel = true;
System.Windows.Forms.MessageBox.Show("This data is read-only.");
}
.NET Framework Security
- Full trust for the immediate caller. This member cannot be used by partially trusted code. For more information, see Using Libraries from Partially Trusted Code.