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.
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
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)
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)
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


Comments

All comments about this page:


Comment by: MSimms (3-12-2012 15:37:59) deeplink to this comment

2013 is still beta, no ?
Why doesn't the Excel team incorporate the fix into Excel 2013 ?
Seems crazy to have to implement all of that VBA+API coding to solve such a basic issue.


Comment by: Jan Karel Pieterse (3-12-2012 17:35:18) deeplink to this comment

Hi MSimms,

No, 2013 has already been released for production. Large companies and people with MSDN subscriptions can already install the final product. The consumer market will probably be able to buy 2013 early next year.

About the fix: This behaviour is "by design" and is identical to how Word behaves already. No chance we'll get a fix I expect.


Comment by: MSimms (3-12-2012 20:58:35) deeplink to this comment

Unbelievable. A much better approach would be to have SDI/MDI option settings that are selectable and settable via VBA and Ribbon/GUI.
I do see the benefits of SDI, but my preference is for MDI especially if this is going to be a problem for applications....and it is IMHO.
Thanks for the good work on this Jan.


Comment by: Jan Karel Pieterse (4-12-2012 07:34:52) deeplink to this comment

Hi MSimms,

Yes, an option would've been nice but isn't going to happen. We did request that, but given all the other work they did on 2013 there simply wasn't time to even consider it.

O well, it is things like this that keeps me in business.


Comment by: Dutch Gemini (4-7-2013 12:46:09) deeplink to this comment

The link where I found "-8&" is: http://www.codeproject.com/Articles/80255/Add-Your-Control-On-Top-Another-Application


Comment by: Ivor Shaer (22-10-2014 22:54:44) deeplink to this comment

I have various Excel VBA applications where my macros copy a contiguous range of cells from one workbook to another without a problem. However, when I install/run these macros on Excel 2013 I get an error message "This command cannot be used on multiple selections"

The code reads:

Windows("UniversalMaster.xls").Activate
Sheets("Factories").Activate
Range(Range("A1"), Range("A1").SpecialCells(xlLastCell)).Copy
Windows("TrimsMaster.xls").Activate
Sheets("Factories").Activate
ActiveSheet.Paste
Application.CutCopyMode = False

This code has worked for the last eight years over all the versions of Excel. Why does it break down on Excel 2013.

Once the macro has failed, if I try and "manually" copy and paste, i.e. not using VBA, I get the same error!!

HELP, PLEASE!!


Comment by: Jan Karel Pieterse (23-10-2014 07:01:24) deeplink to this comment

Hi Ivor,

Does the command also fail BEFORE the code has been run?
Does the selection you are trying to copy contain ANY merged cells by any chance?


Comment by: DCL (24-3-2016 13:41:41) deeplink to this comment

It is a big dispointing after upgrading to excel2013 just because of this issue. My own VBA application copy/compare data between two workbooks. Because of this issue, my applicatio is useless.

I really want to go bakc to excel2010.
Wha's new in 2013? Nothing benefits feelable but such a notorious problem. What a shit!


Comment by: Jan Karel Pieterse (27-3-2016 20:16:05) deeplink to this comment

Hi DCL,

Excel 2013 has some new stuff regarding pivottables (you can now easily join related tables, so you can for example get customer names from your customers table and report their sales from the sales table.


Have a question, comment or suggestion? Then please use this form.

If your question is not directly related to this web page, but rather a more general "How do I do this" Excel question, then I advise you to ask your question here: www.eileenslounge.com.




To post VBA code in your comment, use [VB] tags, like this: [VB]Code goes here[/VB].