none
How to handle Insert/Delete Table Row user action in excel vsto? RRS feed

  • Question

  • Hi,

    I am developing a excel addin where I provide a ribbon button and when clicked on it, addin shows a user control which has a list of items. When I double click on a item, I am creating a table (getting data from server and created a list of objects as datasource). User can edit/Insert a Row at any place in the table and delete any Row in the table.

    Question 1 : 

    How do I handle inset/delete rows of a table? I should be able to get the position at which the row was inserted, also I should be able to find if the row was inserted before or after a particular Row. Same thing for delete as well. I tried googling for the same and some people have mentioned that we can count the number of rows and then compare the rows in Worksheet.Change event and use it to figure out if a row was inserted or deleted, but the problem with this is I can't get the location at which the row was Inserted/Deleted.

    Question 2:

    How do I handle edit event, I know this question sounds lame, but I am still learning vsto. I am actually using Worksheet.Change, but this event gets triggered everytime when something changes in the worksheet.

    Question 3 :

    Is there any websites/Documentation which list all the events that can be handled in vsto, like worksheet.Change etc..?

    Any help would be greatly appreciated.

    Tuesday, June 6, 2017 10:31 AM

Answers

  • Hi Suresh351994,
    There is no event like table_edit  which could capture the edit operation. We could still use Worksheet_change event to identify if we only edit in the table. You could combine the code in Question 1 and Question 2 to check if the target belongs to the table when there is no row added/deleted, here is the example.
       Dim tb As ListObject
       Set tb = ActiveSheet.ListObjects(1)
       Debug.Print Target.Rows.count
       If tb.Range.Cells.count > count Then
       Debug.Print "There is one row added. Row index is " & Target.Row
       count = tb.Range.Cells.count
       ElseIf tb.Range.Cells.count < count Then
       Debug.Print "There is one row deleted. Row index is " & Target.Row
       ElseIf tb.Range.Cells.count = count then
       Debug.Print "There is no row added/deleted, I edit value on " & Target.Address
       count = tb.Range.Cells.count
       End If
    Best Regards,
    Terry

    Friday, June 9, 2017 5:55 AM

All replies

  • Hi Suresh351994,

    >> Question 1

    I don't find event which handle insert/delete row of a table. I also recommend you use Worksheet.Change. You could use a global variable to record the table's cells count. In Worksheet.Change event, you could compare the count with latest cells count to confirm if there is a row be inserted or deleted. Here is the example.

    Dim count As Integer

    Sub Init()

    count = ActiveSheet.ListObjects(1).Range.Cells.count

    End Sub

    Private Sub Worksheet_Change(ByVal Target As Range)

       Dim tb As ListObject

       Set tb = ActiveSheet.ListObjects(1)

       If tb.Range.Cells.count > count Then

       Debug.Print "There is one row added. Row index is " & Target.Row

       count = tb.Range.Cells.count

       ElseIf tb.Range.Cells.count < count Then

       Debug.Print "There is one row deleted. Row index is " & Target.Row

       count = tb.Range.Cells.count

       End If

    End Sub

    >> Question 2

    You could confirm if the target range is in the table range and then run the code. Such as

       If Not Intersect(Target, ActiveSheet.ListObjects(1).Range) Is Nothing Then

       Debug.Print "This is in table"

       End If

     

    >>Question 3

    Microsoft doesn't provide such documents or website, you could check specific object's events in  MSDN library website

     

    Best Regards,

    Terry

    Wednesday, June 7, 2017 11:31 AM
  • Hi Terry,

    Thanks for the help. I am now clear with Question 1 and Question 3.

    Regarding Question 2, Your solution just says if the edited cell belongs to the table or not. But my question was, how will I know if it was an edit event?

    Suppose I edited a cell which belongs to, say, Table1. Now I have a WorkSheet.Change Event handler which caches the event on edit. Now how can I make sure that it was an edit operation in change event handler method? Because insert row and delete row also comes Change event, how do I differentiate edit event from rest of the events that comes to Change event handler?

    Thursday, June 8, 2017 10:31 AM
  • Hi Suresh351994,
    There is no event like table_edit  which could capture the edit operation. We could still use Worksheet_change event to identify if we only edit in the table. You could combine the code in Question 1 and Question 2 to check if the target belongs to the table when there is no row added/deleted, here is the example.
       Dim tb As ListObject
       Set tb = ActiveSheet.ListObjects(1)
       Debug.Print Target.Rows.count
       If tb.Range.Cells.count > count Then
       Debug.Print "There is one row added. Row index is " & Target.Row
       count = tb.Range.Cells.count
       ElseIf tb.Range.Cells.count < count Then
       Debug.Print "There is one row deleted. Row index is " & Target.Row
       ElseIf tb.Range.Cells.count = count then
       Debug.Print "There is no row added/deleted, I edit value on " & Target.Address
       count = tb.Range.Cells.count
       End If
    Best Regards,
    Terry

    Friday, June 9, 2017 5:55 AM