Keeping Userforms on top of SDI windows in Excel 2013 and up

Pages in this article

1. MDI vs SDI
2. The code

Difference between MDI and SDI

The difference between MDI and SDI can be seen easiest when one tiles the windows of workbooks open in Excel:

Excel 2010 showing two workbooks side by side in the same application window.

The two workbooks are shown within one main Excel window.

With Excel 2013 this radically changes; Excel 2013 behaves just like Word: each workbook is shown in its own "application" window (I've put application between quotes, because behind the scenes there is still only one Excel application loaded). Like so:

Excel 2013 showing two workbooks in two virtually separate Excel windows

As you can see, each workbook has a full ribbon at its top. As if it were opened in its own Excel application instance (note that Task manager shows only one Excel.exe process).

Unfortunately this affects how modeless userforms behave. In Excel versions up to and including Excel 2010, a modeless userform would stay on top of all Excel windows by default. In Excel 2013 however, a modeless userform will only be visible on top of the workbook window that was active when the userform was shown.

The screenshot below depicts two workbooks open in Excel 2013. The right-most workbook has a button that calls up a modeless userform. The form nicely pops up, so it seems:

Excel 2013 showing two workbooks, userform called from left-most workbook (active workbook)

However, since the userform is modeless, you can click on the left-hand workbook's window. With Excel 2010 and before, the userform would stay on top of both windows but with Excel 2013's new SDI interface, the Userform is covered by the second workbook's window:

Excel 2013 showing two workbooks, userform called from left-most workbook (right-most workbook is active)

Next: Explanation of the code involved to solve the problem