Most Valuable Professional


View Jan Karel Pieterse's profile on LinkedIn subscribe to rss feed
Subscribe in a reader

Subscribe to our mailing list

* indicates required

Audit !!!

Check out our RefTreeAnalyser
the ultimate Excel formula auditing tool.

Trainings

Excel VBA Masterclass (English)
Excel VBA for Financials (Dutch)

Third party tools

Speed up your file

FastExcel
The best tool to optimise your Excel model!

Repair your file

Stellar Phoenix Excel Repair
Best tool to repair corrupt Excel sheets and objects
Home > English site > Articles > Keep Userform On Top > MDI vs SDI

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

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

Showing last 8 comments of 9 in total (Show All Comments):

 


Comment by: Jan Karel Pieterse (12/3/2012 5:35:18 PM)

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 (12/3/2012 8:58:35 PM)

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 (12/4/2012 7:34:52 AM)

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 (7/4/2013 12:46:09 PM)

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

 


Comment by: Ivor Shaer (10/22/2014 10:54:44 PM)

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 (10/23/2014 7:01:24 AM)

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 (3/24/2016 1:41:41 PM)

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 (3/27/2016 8:16:05 PM)

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.

Please enter your name (required):

Your e-mail address (optional but if you want me to respond it helps!; will not be shown, nor be used to send you unsolicited information):

Your request or comment:

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