locked
Event Handler for Excel ActiveSheet SelectionChange (C#) RRS feed

  • Question

  • Hi, I am making an excel template where the user needs to select a range on any worksheet in ThisWorkbook. I want to event handle when the user changes the range selection in whatever sheet is active at the time. I know how to handle a range change for a specific sheet, for example for sheet1 you would use:

    Globals.Sheet1.SelectionChange += new Microsoft.Office.Interop.Excel.DocEvents_SelectionChangeEventHandler(Sheet1_SelectionChange);

    I want to do this, but with the current active sheet, not a specific sheet. It seems like it would be something along the lines of:

    (Microsoft.Office.Interop.Excel.Worksheet)Globals.ThisWorkbook.ActiveSheet.SelectionChange += ....
    but it will not let me make a SelectionChange event for the ActiveSheet. I am using VSTO 2008 and an Excel 2007 template. Any help?
    Monday, June 28, 2010 1:56 PM

Answers

  • I think you just needed to adjust your cast operation in your code to get it to work. The following revision works fine for me.

        private void ThisWorkbook_Startup(object sender, System.EventArgs e)
        {
          ((Excel.Worksheet)Globals.ThisWorkbook.ActiveSheet).SelectionChange += new Excel.DocEvents_SelectionChangeEventHandler(activeSheet_SelectionChange);
        }
    
        void activeSheet_SelectionChange(Excel.Range Target)
        {
          // Put your code here
        }

    Note that this code handles the SelectionChange event only for whatever worksheet was the active worksheet when you attached the event handler to the event. The event handler is not passed the Worksheet object that changed; it is assumed that your code already knows this (for example, if you're just working in the context of a particular Sheet class in your project).

    If by "I want to event handle when the user changes the range selection in whatever sheet is active at the time." you mean that you want handle the SelectionChange of any worksheet in your project, you can alternatively follow incre-d's suggestion an handle the Application.SheetSelectionChange event. This event will give you the Worksheet object that was changed.

        private void ThisWorkbook_Startup(object sender, System.EventArgs e)
        {
          Globals.ThisWorkbook.Application.SheetSelectionChange += new Excel.AppEvents_SheetSelectionChangeEventHandler(Application_SheetSelectionChange);
        }
    
        void Application_SheetSelectionChange(object Sh, Excel.Range Target)
        {
          // Put your code here
        }

    This posting is provided "AS IS" with no warranties, and confers no rights.
    • Marked as answer by s2t Tuesday, June 29, 2010 2:45 PM
    Monday, June 28, 2010 6:45 PM
    Answerer

All replies

  • The events are divided in the Application object under Workbook<blah> and Sheet<blah>

    I think you are looking for the Application.SheetSelectionChange += ..

    Monday, June 28, 2010 2:35 PM
  • I think you just needed to adjust your cast operation in your code to get it to work. The following revision works fine for me.

        private void ThisWorkbook_Startup(object sender, System.EventArgs e)
        {
          ((Excel.Worksheet)Globals.ThisWorkbook.ActiveSheet).SelectionChange += new Excel.DocEvents_SelectionChangeEventHandler(activeSheet_SelectionChange);
        }
    
        void activeSheet_SelectionChange(Excel.Range Target)
        {
          // Put your code here
        }

    Note that this code handles the SelectionChange event only for whatever worksheet was the active worksheet when you attached the event handler to the event. The event handler is not passed the Worksheet object that changed; it is assumed that your code already knows this (for example, if you're just working in the context of a particular Sheet class in your project).

    If by "I want to event handle when the user changes the range selection in whatever sheet is active at the time." you mean that you want handle the SelectionChange of any worksheet in your project, you can alternatively follow incre-d's suggestion an handle the Application.SheetSelectionChange event. This event will give you the Worksheet object that was changed.

        private void ThisWorkbook_Startup(object sender, System.EventArgs e)
        {
          Globals.ThisWorkbook.Application.SheetSelectionChange += new Excel.AppEvents_SheetSelectionChangeEventHandler(Application_SheetSelectionChange);
        }
    
        void Application_SheetSelectionChange(object Sh, Excel.Range Target)
        {
          // Put your code here
        }

    This posting is provided "AS IS" with no warranties, and confers no rights.
    • Marked as answer by s2t Tuesday, June 29, 2010 2:45 PM
    Monday, June 28, 2010 6:45 PM
    Answerer