none
Worksheet does not have a VSTO object when no control is on the sheet RRS feed

  • Question

  • Hi,

    I am working on a workbook customization for Excel 2013.

    I am trying to add buttons to a at runtime on a Worksheet (Microsoft.Office.Tools.Excel.Controls.Button type).  I need to do this, because when copying a template sheet to get a default layout, the buttons on the new sheet cannot be clicked on the initial run (aka when the sheet has just been copied).  On the second run (reopening the file after saving), the buttons on the Template sheet are messed up, but the buttons on the copied sheet are clickable and fully functionnal.

    In order to work around that issue, I am trying to create those buttons when the Workbook opens with the following snippet (Keep in mind that Excel references Microsoft.Office.Interop.Excel and VSTO references Microsoft.Office.Tools.Excel ):

    private void ThisWorkbook_Startup(object senderSystem.EventArgs e)
            {
                // Create a new worksheet for a new day
                if (!this.Worksheets.Cast<Excel.Worksheet>().Any(x => x.Name == DateTime.Today.ToShortDateString()))
                {
                    Excel.Worksheet ws = Globals.ThisWorkbook.Worksheets["Template"];
                    ws.Copy(this.Worksheets[1]);
                    Excel.Worksheet newDayWs = Globals.ThisWorkbook.Worksheets[1];
     
                    newDayWs.Name = DateTime.Today.ToShortDateString();
     
                    VSTO.Worksheet nativeNewSheet = Globals.Factory.GetVstoObject(newDayWs);
     
                    foreach (Excel.Name rng in nativeNewSheet.Names)
                    {
                        if (rng.Name.ToLower().Contains("button"))
                            nativeNewSheet.Controls.AddControl(new Microsoft.Office.Tools.Excel.Controls.Button(), rng.RefersToRange.MergeArea ?? rng.RefersToRangerng.Name);
                    }
                }
            }

    If there is no control that was added with the designer on the template sheet, I cannot go past the line, as the sheet does not have an underlying VSTO object.  With a control, it works like a charm.

    VSTO.Worksheet nativeNewSheet = Globals.Factory.GetVstoObject(newDayWs);

    I am doing something wrong here ?

    Friday, February 12, 2016 11:03 PM

Answers

  • I finally opted to create my buttons at runtime and leaving a non-visible button to force the Interop Worksheet have the VSTO object.
    • Marked as answer by TremcarDev Monday, February 15, 2016 2:30 PM
    Monday, February 15, 2016 2:30 PM

All replies

  • Hi TremcarDev,

    >> I cannot go past the line, as the sheet does not have an underlying VSTO object

    Based on your description, I could reproduce your issue. I am not sure the root cause of this. For your original requirement, you want to copy worksheet with butts, am I right? With Microsoft.Office.Tools.Excel.Controls.Button, I could reproduce your issue that the buttons on new sheet cannot be clicked.

    To work around this issue, I suggest you create a xlsm excel file with a button, apply macro for this button event, then you could create the workbook vsto project based on this exist excel file. With my test, the button click would work correctly after copying.

    Best Regards,

    Edward


    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 15, 2016 7:23 AM
  • I finally opted to create my buttons at runtime and leaving a non-visible button to force the Interop Worksheet have the VSTO object.
    • Marked as answer by TremcarDev Monday, February 15, 2016 2:30 PM
    Monday, February 15, 2016 2:30 PM