Excel caches the standar commandbar button of an addin RRS feed

  • Question

  • Hi,
    I have an excel automation add-in, which is loaded on demand. When the user opens a spreadsheet,
    and types a  =Addin_func() formula, my addin gets loaded up and along with it, a button gets added to
    the standard commandbar. This button is related to my addin, and clicking this button shows up a windows form, through which the user can enter a few parameters, call my addin, and populate some cells on the spreadsheet.

    1. After installation on a user machine, when the user opens excel for the first time, my addin is not
        loaded and also there is no addin-button at the standard commandbar, which is perfectly fine, as my load  
        behavior is 9(load on demand).
    2. Now, the user types an =Addin_func() formula, and the button comes up. When the user clicks on the
        button, the form also comes up and everything works perfectly fine.

    3. But, when the user closes excel and re-opens it again, all the problems start.
        - The button comes up on the standard commandbar, even though the add-in is not loaded. The log
          files confirm that the control has not entered into the OnConnection() or OnstartupComplete()
          methods of the add-in.
        - Clicking on that button does not bring up the form.  Probably Excel, has lost the clickhandler while
          caching the button.
        - Now, when the user types an =Addin_func() formula in an excel cell, the addin gets loaded, the
          formula works, but the button still does not work.

        - It looks like excel caches the button on the standard commandbar, and brings it next time when the user
         opens the spreadsheet, but obviously the even handler associated with the button is lost, and the
         button does not work..

    Can you please help me in this regard. I don't see a method in the commandbar object, which can clear its cache etc. I have already tried calling commandBar.Delete() and .Reset() etc, but nothing seems to work.

    Please let me know if you have any suggestions.


    • Moved by Cindy Meister MVP Thursday, March 5, 2009 3:26 PM not a VSTO solution (Moved from Visual Studio Tools for Office to Off-Topic Posts (Do Not Post Here))
    Thursday, March 5, 2009 11:25 AM

All replies

  • This forum supports only VSTO add-ins. Automation add-ins are not part of VSTO and are not supported in this forum. VSTO add-ins do not have OnConnection or OnStartupComplete events. COM Add-ins based on the IDTExtensibility2 interface have this. These add-ins are supported in the office.developer.add_ins newsgroup, to which you'll find a link in the forum's Please Read First message.

    When you post there, you should include additional information:

    1. The version of Office

    2. The code that manages the commandbar button: how and when it's generated as well as how and when it's removed. (I suspect you don't have this last...)

    Excel (and Word) don't "cache" commandbars or controls; they save them in a document (Excel workbook, in this case). The name of that workbook will depend on the version of Excel involved, but it would be something like Personal.xlb. Certainly, there is no hook-up to the event handler when Excel is quit then restarted - there never is with COM add-ins. Your code must take care of hooking back up and/or removing the control in the scenario you describe (load on demand).

    You might also see if setting the Temporary parameter (CommandBar.Controls.Add method) to true helps you at all. (It wouldn't for Word, but might for Excel).
    Cindy Meister, VSTO/Word MVP
    Thursday, March 5, 2009 3:26 PM