Excel 2007: Ribbon tabs (TabAddIns) disappear from doc level customizations if user opens and closes a doc running in the same instance of Excel

Symptoms:

Consider a scenario where you have developed a Document level customization for Excel 2007 using Visual Studio Tools for Office 3.0 and you have customized the default tab (TabAddIns) to add any Office Ribbon control.

You will notice that the “AddIns” tab is no longer available, if you open or create a new Excel workbook in the same instance of the customized Workbook and then re-activate the customized workbook.

Below is how you can reproduce the problem:-

1. Create a new Excel 2007 Document Level Solution.

2. Add a new Ribbon to the solution.

3. Add Office Button to the default group created (group1) in the Ribbon Designer.

4. Run the Solution by pressing F5.

5. Select the Add-Ins tab and you will see the button that you have added

6. Now, Go to File—>New-->Blank Workbook and click on OK or open any other workbook in the same instance of Excel.

7. If you re-activate your customized workbook, you will notice that the AddIns tab is no longer visible.

Cause

This is a known problem with Excel 2007 VSTO workbook customizations.

 

Workaround:

As a workaround you can perform some action which will toggle the Add-Ins tab on and off when the workbook is activated. This will reset the visibility of the Add-Ins tab.  Below code snippet demonstrates the approach.

 void Application_WindowActivate(Microsoft.Office.Interop.Excel.Workbook Wb, Microsoft.Office.Interop.Excel.Window Wn)
{
Globals.ThisWorkbook.Application.CommandBars["Standard"].Controls[1].OnAction = "abc";
Globals.ThisWorkbook.Application.CommandBars["Standard"].Controls[1].OnAction = "";
}