Bound ListObject insert problem

I have a ListObject on a worksheet that is bound to a datatable. If a user inserts a row using Excel, the blank row inserts just fine. But if the user then enters data in one of the cells and tabs out or uses the enter key, the rest of the cells in the newly added row instantly take on the value of the cells in the row directly beneath. Is there any way to stop this behavior I am doing something fundamentally wrong

Here is a code snippet that will reproduce the behavior. Create a new VSTO project and use this code for the Sheet1 startup event, putting a ListObject on Sheet1 named "testList":

private void Sheet1_Startup(object sender, System.EventArgs e)
{

DataSet ds = new DataSet();
DataTable dt = new DataTable("Test");

dt.Columns.Add("ID");
dt.Columns.Add(
"Name");

DataRow dr = dt.NewRow();
dr[
"ID"] = 1;
dr[
"Name"] = "Sam";
dt.Rows.Add(dr);

dr = dt.NewRow();
dr[
"ID"] = 2;
dr[
"Name"] = "Dave";
dt.Rows.Add(dr);

dr = dt.NewRow();
dr[
"ID"] = 3;
dr[
"Name"] = "Joe";
dt.Rows.Add(dr);

dr = dt.NewRow();
dr[
"ID"] = 4;
dr[
"Name"] = "Brian";
dt.Rows.Add(dr); 

ds.Tables.Add(dt);

BindingSource bs = new BindingSource(ds, "Test");

this.testList.DataSource = bs;

}

Run the project and then try to insert a row between Joe and Dave using the Spreadsheet. Type a new ID in the empty row and hit enter. The Name column of the new row  will get the value from the row below. It will work the same if you type a name and no ID, it will take the ID from the row below.

Thanks for any help or suggestions!

Dave



Answer this question

Bound ListObject insert problem

  • Kjell Inge

    I have discovered this only happens when right-clicking on the row marker in Excel and using insert from there. If you use the menu item or right-click inside the ListObject the problem does not occur.

    Dave


  • samr

    Dave,

    It looks like you have found a bug. Thank you. I have logged the bug. Unfortunately, I don't know of any work around for this.

    Janet Robinson
    Program Manager
    VSTO Team



  • D06147

    I believe I found a bug in excel. Whenever I use SelectionChange in a macro, the past functionality is intermittently grayed out. I want it to perform the following. Whenever a cell is selected from a drop down combo box, it inputs a date/time stamp in the cell to the right. Any ideas or suggestions.

    Option Explicit

    Private Sub Calendar1_Click()
    StoreCalandarValueInActiveCell
    End Sub

    Private Sub CommandButton1_Click()
    ' Call subrutine

    ShowCalandar
    End Sub

    Private Sub CommandButton2_Click()
    ShowCalandar
    End Sub

    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Debug.Print (ComboBox1.LinkedCell)
    Call WorksheetSelectionChange_SetDropdown(Target, ComboBox1)
    End Sub

    Private Sub Worksheet_Change(ByVal Target As Excel.Range)
    TimeStampActiveCell Target
    End Sub

    Thanks
    -B

  • Bound ListObject insert problem