Excel 2013 single document interface (SDI): How to rebuild your task panes to support it
If you’re an avid user of Excel, you would’ve noticed that Excel 2013 has moved from being a Multi document interface (MDI) application to being a Single document interface (SDI) application.
Essentially what this means is that one Excel window holds one Excel workbook, whereas in the past you could have multiple Excel workbooks open in one Excel window.
These things are always easier to explain with the help of an image. Below is an example of how you can open several workbooks in Excel 2010.
Multiple workbooks open in Excel 2010:
Whereas with Excel 2013, you can still tile your Excel workbooks, but each workbook will be in its own window and each workbook will have its own Ribbon.
Multiple workbooks open in Excel 2013:
This change does have an impact for your Excel add-ins because all the workbook level events, methods and properties now operate on the top level window.
How this change is affecting your custom Excel task panes
From an end-user’s point of view this might not be too much of a big deal since Word has used a single document interface for years. However, from a developer’s perspective this change does affect the usability of your custom task panes considerably.
Consider the above examples, but with a custom task pane in Excel 2010:
In Excel 2010 our custom task pane with multiple open workbooks looks fine and it is pretty clear what it does and that it is applicable to all the open workbooks.
In Excel 2013, however, the same custom task pane can be very confusing as reflected in the following image:
You’ll notice that in Excel 2013, each workbook now has its own version of your custom task pane and depending on the type of functionality your task pane provides, this “look” can be very confusing to the user. In my example, the task pane gives the user the ability to copy worksheets between the open workbooks, whereas its purpose is pretty obvious in Excel 2010, but not in Excel 2013.
How do we get custom task panes to work properly in Excel 2013?
I was a little stumped as to what approach to take in order to make my custom Excel 2013 task pane behave in a similar fashion as it does in Excel 2010. Luckily, my colleagues Dmitry and Gennady came up with a simple, yet brilliant idea!
First off, add a new Windows Form to your project. We are using Add-in Express for Office and .net.
Next, add a new User Control to your project. This control will be the main UI for our task pane and by using a user control, it will enable us to re-use the logic on both the Windows Form as well as the Excel Task Pane.
Next, open the AddinModule designer surface and add a new Microsoft Excel events component to the designer surface.
Add a new event handler for the WindowResize event by double-clicking next to it in the Microsoft Excel event components’ list of events.
Add the following code to the event handler:
private void adxExcelEvents_WindowResize(object sender, object hostObj, object window) { Excel.Window currentWindow = null; try { currentWindow = (Excel.Window)window; if (taskPaneForm != null) { if (currentWindow.WindowState == Excel.XlWindowState.xlMinimized) { if (IsAllWindowsMinimized()) taskPaneForm.Hide(); } else { taskPaneForm.Show(); } } } finally { if (currentWindow != null) Marshal.ReleaseComObject(currentWindow); } }
The taskPaneForm is declared at the top of the AddinModule.cs class. Switch back to the AddinModule designer and add a new RibbonTab component to the designer surface.
Add a Ribbon button to the new ribbon tab. This button will be used to show either the Task Pane or the Windows form, depending on which version of Excel the user is using.
Add the following code to the Ribbon buttons’ OnClick event:
private void showTaskPaneRibbonButton_OnClick(object sender, IRibbonControl control, bool pressed) { if (this.HostMajorVersion > 14) { if (taskPaneForm == null) { taskPaneForm = new frmTaskPane(this.ExcelApp as Excel._Application); taskPaneForm.TopMost = true; } taskPaneForm.Show(); } else { AddTaskPane(); taskPaneItem.ShowTaskPane(); } }
The code for the AddTaskPane method will be similar to the following:
private void AddTaskPane() { if (adxExcelTaskPanesManager1.Items.Count > 0) return; taskPaneItem = new AddinExpress.XL.ADXExcelTaskPanesCollectionItem(); taskPaneItem.TaskPaneClassName = "ExcelSDI.ADXExcelTaskPane1"; taskPaneItem.AlwaysShowHeader = true; taskPaneItem.UseOfficeThemeForBackground = true; taskPaneItem.CloseButton = true; taskPaneItem.Position = AddinExpress.XL.ADXExcelTaskPanePosition.Left; adxExcelTaskPanesManager1.Items.Add(taskPaneItem); }
We’re now ready to build, register and run our add-in. You’ll notice that the Custom Task Pane will work as expected in any Excel version prior to 2013. If the user is using Excel 2013 the Windows form we’ve added earlier will show the content of the task pane as a floating window. The window will always be on top of any other windows because we’ve set its TopMost property to True. The final result for Excel 2013 should resemble the following:
Floating custom task pane in Excel 2013:
Now, I have to state that this is not the be-all and end-all to working around the way Excel 2013 handles custom Task panes, but merely some food for thought. If you have solved a similar challenge or have some thoughts on getting around this with Excel 2013, please do share it in the comments below.
Thank you for reading. Until next time, keep coding!
Available downloads:
This sample Outlook add-in was developed using Add-in Express for Office and .net:
Excel 2013 Task Pane (C# sample)
6 Comments
That takes care of displaying the task pane, where are your ribbon controls for putting into effect all the stuff you might want to do with the Workbook Copier function? I see no ribbon related to that function.
I thought an advantage of SDI would be that you could build a custom ribbon for a workbook (template) with a single worksheet, sheet tabs, gridlines, scroll bars turned off, scrollarea controlling the viewing region, controls or hyperlinks within the worksheet which would trigger the functionality – or perhaps a custom ribbon made exactly for operations you are trying to do with your the task pane.
Hi Bill,
You are right, this article explains the difference between Excel 2013 SDI and all earlier Excel versions that were MDI with regard to advanced task panes only. All and by, ribbons in Excel 2013 behave in a similar manner – each Excel workbook has its own ribbon. You can hide/show your ribbon controls for a particular workbook by using the WorkbookActivate, WindowActivate events.
Encountering this problem brought me here.
In the future, will Addin-Express be able to handle taskpanes in SDI without the programmer having to use the fix which has been described here?
Jonatan,
Pieter just pointed out that such problem exists. There can be several ways to resolve it depending on how you build the UI and logic of your add-in.
In one of our commercial add-ins we employed the technique described in the article. There are still a few pitfalls here but we are working on this.
{“Pieter just pointed out that such problem exists.”}
That was also my impression.
I am glad to hear that you are working on it!
Thank you for the hasty reply. It came much faster than I expected.
I too had many issues adapting an add-in developed for prior versions, primarily because my interface relies heavily on a custom ribbon tab. I tried storing button settings and re-populating these settings based upon workbook activation events. In the end, I found it easiest to force Excel 2013 to open a new instance and therefore a default ribbon whose controls are not tied to other workbook instances. Thought I would post in case it helps anybody..
Shell(“Excel.exe /x”, AppWinStyle.MinimizedNoFocus)