CODEWITHSUNDEEP
c#excelevent-handlingvsto
Jeremy Thompson
Jeremy Thompson

Reputation: 65534

Excel button event doesn't fire when spreadsheet is re-opened

When I add a button or link to a spreadsheet the click event works. However when I save the spreadsheet, close it and reopen it the event doesn't fire.

AddControl(xlApp, ((TaskPaneView)this.Parent).factory, "text", "A1");

public void AddControl(Microsoft.Office.Interop.Excel.Application xlApp, Microsoft.Office.Tools.Excel.ApplicationFactory factory, string controlText, string address)
{
    Microsoft.Office.Interop.Excel.Worksheet nativeWorksheet = xlApp.ActiveWorkbook.ActiveSheet;
    Microsoft.Office.Tools.Excel.Worksheet vstoWorksheet = factory.GetVstoObject(nativeWorksheet);
    var btn = new Microsoft.Office.Tools.Excel.Controls.Button();
    btn.Name = "link1";
    btn.Text = controlText;
    btn.Click += new EventHandler(btn_Click);
    vstoWorksheet.Controls.AddControl(btn, nativeWorksheet.Range[address], controlText);
}

void btn_Click(object sender, EventArgs e)
{

}

Is there a easy way to hook up the event again when the WorkBook is opened?

I'm thinking I will have to search the workbook for the buttons and AddHandler's manually.

Edit: This is documented but there is no mention of the problem after closing the spreadsheet: Adding Controls to a Worksheet at Run Time in an Application-Level Project

Upvotes: 1

Views: 1641

Answers (1)

Jeremy Thompson
Jeremy Thompson

Reputation: 65534

enter image description here

When a document is saved and then closed, all dynamic host controls are removed from the document. Only the underlying native Office objects remain behind..

I might do it another way now, create VBA buttons with code to tap into the Add-In functionality:

VBA code:

Dim oAddin As COMAddIn
Dim oCOMFuncs As Object
Set oAddin = Application.COMAddIns(ExcelAddInNameSpace")
Set oCOMFuncs = oAddin.Object
oCOMFuncs.CallAFunctionOfAddIn

Or another way using context menu's (when over NamedRanges).

More info from MSFT:

But here comes the problem: if you save the workbook, open it on either a machine with the add-in intalled or a clean machine, the button doesn't work this time!

So we can see that adding controls onto document's surface via add-in doesn't make a lot of sense as the controls may loss its functionality. This is why we generally suggest to use Custom Task Pane instead of adding controls onto surface of document. Have a look at following documents:

http://msdn.microsoft.com/en-us/library/aa942864.aspx

http://msdn.microsoft.com/en-us/library/aa338197(v=office.12).aspx

Upvotes: 1

Related Questions