Copy Paste in Excel VSTO is removing change event and event doesn't execute

Question

Change event is firing fine if I copy paste from A2 cell to B2 cell but when I do cut and paste it doesn't execute the change event, I think event is getting removed during cut and paste. Following is the VSTO code.

I tried other events also like deselect or selectionchange but they are also not working for cut and paste of data from A2 cell to B2 cell.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Xml.Linq;
using Excel = Microsoft.Office.Interop.Excel;
using Office = Microsoft.Office.Core;
using Microsoft.Office.Tools.Excel;
using System.Windows.Forms;
using ExcelVSTO.Service;
namespace ExcelVSTO.UI
{
    public partial class ThisAddIn
    {
        #region "Private Variables"
        Worksheet controlWorksheet;       
        Microsoft.Office.Tools.Excel.NamedRange cellFrom;
        Microsoft.Office.Tools.Excel.NamedRange cellTo;
        string cellFromValue;
        #endregion  

        #region "Events"
        private void ThisAddIn_Startup(object sender, System.EventArgs e)
        {

            controlWorksheet = Globals.Factory.GetVstoObject(Globals.ThisAddIn.Application.ActiveWorkbook.ActiveSheet);

            if (controlWorksheet != null)
            {
                cellFrom = controlWorksheet.Controls.AddNamedRange(controlWorksheet.Range["A2"], "cellFrom");

                cellTo = controlWorksheet.Controls.AddNamedRange(controlWorksheet.Range["B2"], "cellTo");

                cellFrom.Value2 = "FromCell";

                cellTo.Value2 = "ToCell";

                cellFrom.Change += new Microsoft.Office.Interop.Excel.DocEvents_ChangeEventHandler(changesRange_ChangeFrom);

                cellTo.Change += new Microsoft.Office.Interop.Excel.DocEvents_ChangeEventHandler(changesRange_ChangeTo);


            }

        }

        void changesRange_ChangeFrom(Excel.Range Target)
        {
            string cellAddress = Target.get_Address(Microsoft.Office.Interop.Excel.XlReferenceStyle.xlA1);
            cellFromValue = Target.Value2 == null ? string.Empty : Target.Value2.ToString();
        }
        void changesRange_ChangeTo(Excel.Range Target)
        {
            string cellAddress = Target.get_Address(Microsoft.Office.Interop.Excel.XlReferenceStyle.xlA1);
            string cellFromNewVal = cellFrom.Value2 == null ? string.Empty : cellFrom.Value2.ToString();
            string cellToNewVal = Target.Value2 == null ? string.Empty : Target.Value2.ToString();
            //if (string.IsNullOrEmpty(cellFromValue) && cellToNewVal == cellFromValue)
            if (cellToNewVal == cellFromNewVal)
            {
                MessageBox.Show(cellToNewVal + " data is moved to cell " + cellAddress);
                //Send data to API//
                RestAPIService api = new RestAPIService();
                var response = api.SendAlertToApi(cellAddress);
                MessageBox.Show("Api Response - " + response.Status.ToString());
            }            
        }      
        private void ThisAddIn_Shutdown(object sender, System.EventArgs e)
        {

        }
        #endregion

        #region VSTO generated code

        /// <summary>
        /// Required method for Designer support - do not modify
        /// the contents of this method with the code editor.
        /// </summary>
        private void InternalStartup()
        {
            this.Startup += new System.EventHandler(ThisAddIn_Startup);
            this.Shutdown += new System.EventHandler(ThisAddIn_Shutdown);
        }

        #endregion
    }
}

score 0 · Answer 1 · answered May 15 '20 at 10:40

0

Blockquote I think event is getting removed during cut and paste. Following is the VSTO code.

Have you confirmed this by setting a breakpoint and editing the cell manually after a copy paste?

One option that comes to mind is to try to capture a "Paste" event either on a range or the entire sheet, but as that appears to not exist, I found the following thread that might guide you to a solution Excel VBA How to detect if something was pasted in a Worksheet

answered May 15 '20 at 10:40

Adam Tappis

1
3

Thanks Adam, actually its VSTO code and it doesn't have paste or cut events, I figured out the solutions after doing some hit and trials. – Ravish Malhotra May 19 '20 at 11:53
Yeas I realise, that's what I said in my response. VBA or VSTO makes little difference, it's the same object model. The point was the potential of using the undo stack to look for paste events, which you can do with VSTO. Anyway, glad that you found a solution that works for you. – Adam Tappis May 19 '20 at 12:58

score 0 · Answer 2 · answered May 19 '20 at 11:56

I have resolve the issue by adding Change event to From Cell also, following is the code which is working fine for cut and paste and showing alert for those particular cells for which event is added.

namespace ExcelVSTO.UI
{
    public partial class ThisAddIn
    {
        #region "Private Variables"
        Worksheet controlWorksheet;
        Microsoft.Office.Tools.Excel.NamedRange cellFrom;
        Microsoft.Office.Tools.Excel.NamedRange cellTo;
        string cellFromValue;
        #endregion  

        #region "Events"
        private void ThisAddIn_Startup(object sender, System.EventArgs e)
        {
            controlWorksheet = Globals.Factory.GetVstoObject(Globals.ThisAddIn.Application.ActiveWorkbook.ActiveSheet);

            if (controlWorksheet != null)
            {
                cellFrom = controlWorksheet.Controls.AddNamedRange(controlWorksheet.Range["A2"], "cellFrom");

                cellTo = controlWorksheet.Controls.AddNamedRange(controlWorksheet.Range["B2"], "cellTo");

                cellFrom.Value2 = "FromCell";

                cellTo.Value2 = "ToCell";

                cellFrom.Selected += CellFrom_Selected;

                cellTo.Change += new Microsoft.Office.Interop.Excel.DocEvents_ChangeEventHandler(changesRange_ChangeTo);
                cellFrom.Change += new Microsoft.Office.Interop.Excel.DocEvents_ChangeEventHandler(changesRange_ChangeTo);

            }

        }

        private void CellFrom_Selected(Excel.Range Target)
        {
            cellFromValue = Target.Value2 == null ? string.Empty : Target.Value2.ToString();
        }

        void changesRange_ChangeTo(Excel.Range Target)
        {
            string cellAddress = Target.get_Address(Microsoft.Office.Interop.Excel.XlReferenceStyle.xlA1);
            string cellFromNewVal = cellFrom.Value2 == null ? string.Empty : cellFrom.Value2.ToString();
            string cellToNewVal = Target.Value2 == null ? string.Empty : Target.Value2.ToString();
            //if (string.IsNullOrEmpty(cellFromValue) && cellToNewVal == cellFromValue)
            //if (cellToNewVal == cellFromNewVal) // for copy and paste
            if ( cellToNewVal == cellFromValue)
            {

                MessageBox.Show(cellToNewVal + " data is moved to cell " + cellAddress);
                //Send data to API//
                RestAPIService api = new RestAPIService();
                var response = api.SendAlertToApi(cellAddress);
                MessageBox.Show("Api Response - " + response.Status.ToString());
            }
        }
        private void ThisAddIn_Shutdown(object sender, System.EventArgs e)
        {

        }
        #endregion

        #region VSTO generated code

        /// <summary>
        /// Required method for Designer support - do not modify
        /// the contents of this method with the code editor.
        /// </summary>
        private void InternalStartup()
        {
            this.Startup += new System.EventHandler(ThisAddIn_Startup);
            this.Shutdown += new System.EventHandler(ThisAddIn_Shutdown);
        }

        #endregion
    }
}

Copy Paste in Excel VSTO is removing change event and event doesn't execute

2 Answers2