none
TotalRow in ListObject does not refresh after change RRS feed

  • Question

  • Hi,

    My listObject binds to my ViewModel. Also, I set option for my list object to show TotalRow using following statement

    inputTable.ShowTotals = true;
    After I change value in my listobject TotalRow does not refresh. Then I added handler to handle ListObject change events as follows:

    inputTable.Change += new Microsoft.Office.Tools.Excel.ListObjectChangeHandler(list1_Change);

    And the list1_Change method looks as follows:

     void list1_Change(Microsoft.Office.Interop.Excel.Range targetRange,
                Microsoft.Office.Tools.Excel.ListRanges changedRanges)
            {
    
                if (_selectedArea == "Opex")
                {
                    Globals.ThisWorkbook.Application.ScreenUpdating = false;
                    Globals.ThisWorkbook.Application.EnableEvents = false;
                    Globals.ThisWorkbook.Application.EnableAutoComplete = false;
                        inputTable.ShowTotals = false;
                        inputTable.ShowTotals = true;
                    Globals.ThisWorkbook.Application.EnableEvents = true;
                    Globals.ThisWorkbook.Application.EnableAutoComplete = true;
                    Globals.ThisWorkbook.Application.ScreenUpdating = true;
                }
    
            }

    It works Ok but if I change 500 cell rows in one column in my listObject at once (using copy/paste option) it takes a lot  of time to refresh TotalRow because it is done for each row separately instead for all rows at once. :(?

    can anyone help me to make this as it should be..to refresh totalrow instantly after any change .

    Thanks in advance

    Almir


     
    Friday, February 26, 2016 10:24 AM

All replies

  • Hi,

    My listObject binds to my ViewModel. Also, I set option for my list object to show TotalRow using following statement

    inputTable.ShowTotals = true;
    After I change value in my listobject TotalRow does not refresh. Then I added handler to handle ListObject change events as follows:

    inputTable.Change += new Microsoft.Office.Tools.Excel.ListObjectChangeHandler(list1_Change);

    And the list1_Change method looks as follows:

     void list1_Change(Microsoft.Office.Interop.Excel.Range targetRange,
                Microsoft.Office.Tools.Excel.ListRanges changedRanges)
            {
    
                if (_selectedArea == "Opex")
                {
                    Globals.ThisWorkbook.Application.ScreenUpdating = false;
                    Globals.ThisWorkbook.Application.EnableEvents = false;
                    Globals.ThisWorkbook.Application.EnableAutoComplete = false;
                        inputTable.ShowTotals = false;
                        inputTable.ShowTotals = true;
                    Globals.ThisWorkbook.Application.EnableEvents = true;
                    Globals.ThisWorkbook.Application.EnableAutoComplete = true;
                    Globals.ThisWorkbook.Application.ScreenUpdating = true;
                }
    
            }

    It works Ok but if I change 500 cell rows in one column in my listObject at once (using copy/paste option) it takes a lot  of time to refresh TotalRow because it is done for each row separately instead for all rows at once. :(?

    can anyone help me to make this as it should be..to refresh totalrow instantly after any change .

    Thanks in advance

    Almir


     

    Friday, February 26, 2016 10:21 AM
  • It works Ok but if I change 500 cell rows in one column in my listObject at once (using copy/paste option) it takes a lot  of time to refresh TotalRow because it is done for each row separately instead for all rows at once. :(?

    Try setting Calculation to Manual before the copy and paste and back to Auto after the copy and paste.


    Regards, OssieMac

    Saturday, February 27, 2016 10:56 AM
  • Hi Almir,

    >>After I change value in my listobject TotalRow does not refresh

    What’s the result if you create a ListObject (table) manually in excel, then change the value? If it can‘t refresh too, I think the issue is related to your excel file, I suggest that you could post it to Excel IT forum for non-programming issue, you may try it with a new excel file. If just the table that created through the code doesn’t work, please provide the code here.

    Based on my test, the change event trigger only once after I paste the value to the table and the targetRange value is the whole range, so the issue is not related to the change event, also excel object model doesn’t provide the way to change the way how it calculate the data.

    Regards

    Starain


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    Monday, February 29, 2016 7:15 AM
    Moderator