locked
Sheetavtivate Event RRS feed

  • Question

  •  

    Hi Friends

     

    i have 2 question

    1) how can i get the sheet activate event when user click on particular sheet. In VSTO

    2) do we have any event which capture sheet rename event. in VSTO

    Wednesday, June 4, 2008 12:47 PM

Answers

  • The "sheet activate" events available to you are slightly different for application-level add-ins and document-level customizations.

     

    In an application-level add-in, you can handle the Application.SheetActivate, Workbook.SheetActivate, or Worksheet.Activate event. If you handle either of the first two, you'll have to add some logic to the event handler to determine whether the activated sheet is the one you care about. For example, the following C# code attaches the Application.SheetActivate event handler in ThisAddIn_Startup, and displays a message box is the activated sheet is the first sheet in the workbook.

     

        private void ThisAddIn_Startup(object sender, System.EventArgs e)
        {
            this.Application.SheetActivate +=
                new Excel.AppEvents_SheetActivateEventHandler(

                Application_SheetActivate);
        }

     

        void Application_SheetActivate(object Sh)
        {
            if (Type.ReferenceEquals(Sh,

                this.Application.ActiveWorkbook.Sheets[1]))
            {
                System.Windows.Forms.MessageBox.Show(

                    "First sheet was activated.");
            }
        }

     

    If you have a specific Worksheet object that you're tracking, you can alternatively handle the Worksheet.Activate event. In C#, this event isn't immediately accessible on a Worksheet object; you'll first have to cast the Worksheet object to a DocEvents_Event interface before you can access the event and attach an event handler to it. For example:

     

        Excel.Worksheet activeSheet = this.Application.ActiveSheet as

            Excel.Worksheet;


        if (activeSheet != null)
        {
            ((Excel.DocEvents_Event)activeSheet).Activate +=
                new Excel.DocEvents_ActivateEventHandler(ThisAddIn_Activate);
        }

     

    In a document-level project (that is, and Excel Workbook or Excel Template project), you can simply handle the ActivateEvent event of the Sheetn class in your project that corresponds to the worksheet you're interested in. For example, the following code handles the ActivateEvent event of the Sheet1 class, and displays a message box is this sheet is activated.

     

        private void Sheet1_Startup(object sender, System.EventArgs e)
        {
            this.ActivateEvent +=
                new Excel.DocEvents_ActivateEventHandler(Sheet1_ActivateEvent);
        }

     

        void Sheet1_ActivateEvent()
        {
            MessageBox.Show("Sheet1 was activated.");
        }

     

    As far as I know, Excel does not provide any event that is raised when the user renames a worksheet.

     

    I hope this helps,

    McLean Schofield

    Wednesday, June 4, 2008 4:51 PM
    Answerer
  • You haven't specified whether you're creating an Excel workbook project or an Excel add-in project. The process is different for each type of project, because each project supports different types of controls on worksheets.

     

    In an add-in project where you have Office forms controls or ActiveX controls on a worksheet, you can get the worksheet object by using the name as an index into the Worksheets collection. For example:

     

    Code Snippet
    Excel.Worksheet sheet = (Excel.Worksheet)this.Application.ActiveWorkbook.Worksheets["Sheet3"];

     

     

    After you have the Worksheet object, you can get the controls on the worksheet by using one of several any different methods or properties. To get all the controls, you can use the Shapes property, and loop through each Shape to determine which type of control you have.

     

    Code Snippet

    foreach (Excel.Shape shape in shapesCollection)
    {
        if (shape.Type == Microsoft.Office.Core.MsoShapeType.msoFormControl)
        {
            // shape is a form control
        }

        if (shape.Type == Microsoft.Office.Core.MsoShapeType.msoOLEControlObject)
        {
            // shape is an ActiveX control
        }
    }

     

     

    If the sheet contains only ActiveX controls, use the sheet.OLEObjects method, and pass in System.Type.Missing to get all the controls, rather than a specific control.

     

    Code Snippet
    Excel.OLEObjects activeXControls = (Excel.OLEObjects)sheet.OLEObjects(missing);

     

     

    If you're asking how to get the Windows Forms or VSTO NamedRange or ListObject controls on a worksheet in a document-level project, the process is much simpler. Just use the Globals object to access the sheet you're interested in, and then use the Controls property of the worksheet. For example:

     

    Code Snippet

    Microsoft.Office.Tools.Excel.NamedRange nr1 =

        (Microsoft.Office.Tools.Excel.NamedRange)Globals.Sheet1.Controls["myNamedRange"];

     

     

     

    I hope this helps,

    McLean Schofield

    Friday, June 6, 2008 6:27 PM
    Answerer

All replies

  • The "sheet activate" events available to you are slightly different for application-level add-ins and document-level customizations.

     

    In an application-level add-in, you can handle the Application.SheetActivate, Workbook.SheetActivate, or Worksheet.Activate event. If you handle either of the first two, you'll have to add some logic to the event handler to determine whether the activated sheet is the one you care about. For example, the following C# code attaches the Application.SheetActivate event handler in ThisAddIn_Startup, and displays a message box is the activated sheet is the first sheet in the workbook.

     

        private void ThisAddIn_Startup(object sender, System.EventArgs e)
        {
            this.Application.SheetActivate +=
                new Excel.AppEvents_SheetActivateEventHandler(

                Application_SheetActivate);
        }

     

        void Application_SheetActivate(object Sh)
        {
            if (Type.ReferenceEquals(Sh,

                this.Application.ActiveWorkbook.Sheets[1]))
            {
                System.Windows.Forms.MessageBox.Show(

                    "First sheet was activated.");
            }
        }

     

    If you have a specific Worksheet object that you're tracking, you can alternatively handle the Worksheet.Activate event. In C#, this event isn't immediately accessible on a Worksheet object; you'll first have to cast the Worksheet object to a DocEvents_Event interface before you can access the event and attach an event handler to it. For example:

     

        Excel.Worksheet activeSheet = this.Application.ActiveSheet as

            Excel.Worksheet;


        if (activeSheet != null)
        {
            ((Excel.DocEvents_Event)activeSheet).Activate +=
                new Excel.DocEvents_ActivateEventHandler(ThisAddIn_Activate);
        }

     

    In a document-level project (that is, and Excel Workbook or Excel Template project), you can simply handle the ActivateEvent event of the Sheetn class in your project that corresponds to the worksheet you're interested in. For example, the following code handles the ActivateEvent event of the Sheet1 class, and displays a message box is this sheet is activated.

     

        private void Sheet1_Startup(object sender, System.EventArgs e)
        {
            this.ActivateEvent +=
                new Excel.DocEvents_ActivateEventHandler(Sheet1_ActivateEvent);
        }

     

        void Sheet1_ActivateEvent()
        {
            MessageBox.Show("Sheet1 was activated.");
        }

     

    As far as I know, Excel does not provide any event that is raised when the user renames a worksheet.

     

    I hope this helps,

    McLean Schofield

    Wednesday, June 4, 2008 4:51 PM
    Answerer
  • Thanks McLean

     

    this help is good i have found the ans for my first question

     

    is there any method or a way in which you can get list of the control's in worksheet provided the sheet name.

     

    Friday, June 6, 2008 12:59 PM
  • You haven't specified whether you're creating an Excel workbook project or an Excel add-in project. The process is different for each type of project, because each project supports different types of controls on worksheets.

     

    In an add-in project where you have Office forms controls or ActiveX controls on a worksheet, you can get the worksheet object by using the name as an index into the Worksheets collection. For example:

     

    Code Snippet
    Excel.Worksheet sheet = (Excel.Worksheet)this.Application.ActiveWorkbook.Worksheets["Sheet3"];

     

     

    After you have the Worksheet object, you can get the controls on the worksheet by using one of several any different methods or properties. To get all the controls, you can use the Shapes property, and loop through each Shape to determine which type of control you have.

     

    Code Snippet

    foreach (Excel.Shape shape in shapesCollection)
    {
        if (shape.Type == Microsoft.Office.Core.MsoShapeType.msoFormControl)
        {
            // shape is a form control
        }

        if (shape.Type == Microsoft.Office.Core.MsoShapeType.msoOLEControlObject)
        {
            // shape is an ActiveX control
        }
    }

     

     

    If the sheet contains only ActiveX controls, use the sheet.OLEObjects method, and pass in System.Type.Missing to get all the controls, rather than a specific control.

     

    Code Snippet
    Excel.OLEObjects activeXControls = (Excel.OLEObjects)sheet.OLEObjects(missing);

     

     

    If you're asking how to get the Windows Forms or VSTO NamedRange or ListObject controls on a worksheet in a document-level project, the process is much simpler. Just use the Globals object to access the sheet you're interested in, and then use the Controls property of the worksheet. For example:

     

    Code Snippet

    Microsoft.Office.Tools.Excel.NamedRange nr1 =

        (Microsoft.Office.Tools.Excel.NamedRange)Globals.Sheet1.Controls["myNamedRange"];

     

     

     

    I hope this helps,

    McLean Schofield

    Friday, June 6, 2008 6:27 PM
    Answerer
  • Thanks McLean

     

    i am creating excel workbook project and worksheet has been created at runtime.

     

    so how to get list of control from sheet which is created at runtime.

     

    more query's

    1) how to handle move or copy sheet event. i want add some code when user copy the sheet.

    or

    1) if user copy sheet which has some windows forms control. - in new sheet this controls not created.

    how to solve this issue.

     

     

     

     

     

     

     

     

    Tuesday, June 17, 2008 2:02 PM
  • so how to get list of control from sheet which is created at runtime.

     

    What types of controls? Assuming you're referring to Windows Forms controls, there is no way to get these controls on a sheet that is created at runtime, because these sheets don't support Windows Forms controls at all.

     

    When you create a new worksheet at run time in an Excel Workbook project, the worksheet is a Microsoft.Office.Interop.Excel.Worksheet object. These objects do not have any support for Windows Forms controls (they don't have the Controls property).

     

    Only Microsoft.Office.Tools.Excel.Worksheet objects can host Windows Forms controls (by way of the Controls property), but these objects cannot be created at run time. You can only add Microsoft.Office.Tools.Excel.Worksheet objects to your project at design time. When you create your project, the project automatically includes 3 Microsoft.Office.Tools.Excel.Worksheet classes named Sheet1, Sheet2, and Sheet3. If you add additional worksheets to your workbook in the desginer, then Visual Studio automatically generates a new Microsoft.Office.Tools.Excel.Worksheet class for the new worksheet.

     

    how to handle move or copy sheet event. i want add some code when user copy the sheet.

     

    As far as I know, there is no event in the Excel object model that is intended for capturing when the user moves or copies a worksheet, but there might be ways to do this. The best resource for Excel object model questions like this are the Excel newsgroups. Please see the Read First thread for more information about non-VSTO questions and resources.

     

    if user copy sheet which has some windows forms control. - in new sheet this controls not created.

    how to solve this issue.

     

    This is another side effect of the limitation I described earlier: worksheets that are created at run time are Microsoft.Office.Interop.Excel.Worksheet objects that do not support Windows Forms controls. If a user copies a worksheet that has Windows Forms controls (that is, a Microsoft.Office.Tools.Excel.Worksheet object that you configured at design time), the only parts of the Windows Forms controls that get copied into the new worksheet are the container ActiveX controls that are used to host the Windows Forms controls. These ActiveX controls will probably display a bitmap image of the original Windows Forms control, but don't have any functionality in and of themselves.

     

    One way to prevent this is to protect the workbook so that users cannot copy or move worksheets in it. Use the Workbook.Protect method to do this:

     

    // The second parameter (named "Structure") specifies whether users

    // can move or create worksheets.

    Globals.ThisWorkbook.Protect(missing, true, false);

     

    I hope this helps,

    McLean Schofield

    Tuesday, June 17, 2008 6:21 PM
    Answerer
  • Globals.ThisWorkbook.Protect(missing, true, false)

    Will not work in my case it.

     

    I am using this function to create worksheet at runtime

     

    public static Microsoft.Office.Tools.Excel.Worksheet CreateExtendedWorksheet(

    Excel.Worksheet nativeWorksheet, Microsoft.VisualStudio.Tools.Applications.Runtime.IRuntimeServiceProvider runtimeCallback)

    {

    Microsoft.VisualStudio.Tools.Applications.Runtime.IHostItemProvider hip =

    runtimeCallback.GetService(typeof(Microsoft.VisualStudio.Tools.Applications.Runtime.IHostItemProvider)) as Microsoft.VisualStudio.Tools.Applications.Runtime.IHostItemProvider;

    Excel.Workbook workbook = nativeWorksheet.Parent as Excel.Workbook;

    workbook.VBProject.VBComponents.Item(1);

    // Create the new worksheet and return it to calling function.

    return new Microsoft.Office.Tools.Excel.Worksheet(hip, runtimeCallback

    , nativeWorksheet.CodeName, null // Container

    , nativeWorksheet.Name);

    }

    passing Microsoft.Office.Interop.Excel.Worksheet object

    and the add Windows Form Control to the sheet.

     

    is there any way were i can convert Microsoft.Office.Interop.Excel.Worksheet to Microsoft.Office.Tools.Excel.Worksheet

    And get list of controls on that worksheet.

     

    I am getting worksheet in this way at runtime  

    foreach (Excel.Worksheet Sheet in Globals.ThisWorkbook.Worksheets)

    {

    }

     

    And after identifying Sheet created at runtime how to convert Sheet (of Microsoft.Office.Interop.Excel.Worksheet) type to Microsoft.Office.Tools.Excel.Worksheet

     

    Wednesday, June 18, 2008 8:51 AM
  • <<And after identifying Sheet created at runtime how to convert Sheet (of Microsoft.Office.Interop.Excel.Worksheet) type to Microsoft.Office.Tools.Excel.Worksheet>>

     

    McLean explained in his most recent reply that this is not possible. A Microsoft.Office.Tools.Excel.Worksheet can be only be created in the Visual Studio Designer. That's it; no ifs, ands, buts or maybes.

     

    Best you could do would be to create n worksheets at Design time, then make them "Hidden" until the user wants to have a new one in the workbook.

     

    I have to wonder why you think you need so much flexibility? (Creating new sheets, copying/pasting/moving) Would it actually make more sense for you to create a Workbook template type of project? The user can use this to create a new workbook anytime he needs the functionality available in the VSTO customization.

    Wednesday, June 18, 2008 12:06 PM
  • Ah, your scenario is now much clearer. You're using the Worksheet(IHostItemProvider, IRuntimeServiceProvider, String, Object, String) constructor to create a new Microsoft.Office.Tools.Excel.Worksheet object at runtime. This technique is possible in document-level projects for Office 2003, and it is mentioned in several places (such as Eric Carter's/Eric Lippert's book and this white paper). It is my understanding that creating Microsoft.Office.Tools.Excel.Worksheet objects at runtime isn't really an intended and fully tested scenario, which is why Cindy and I were telling you this isn't possible, and why VSTO doesn't provide a more straightforward method to do this. The general recommendation is to create Microsoft.Office.Tools.Excel.Worksheet objects by using the designer.

     

    That said, you should be able to do what you want by caching each Microsoft.Office.Tools.Excel.Worksheet you create at runtime so that you can easily access it later. I'd recommend using a generic Dictionary to store these objects, because you can use the Microsoft.Office.Interop.Excel.Worksheet object as a key to access the corresponding Microsoft.Office.Tools.Excel.Worksheet that you want to store and access later. See my post from 4/25 in this thread for more details and code examples.

     

    Alternatively, Cindy's suggestions are good too.

     

    I hope this helps,

    McLean Schofield

    Wednesday, June 18, 2008 5:30 PM
    Answerer
  • I need this help very much! Thanks !
    Friday, April 11, 2014 7:48 AM