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

Introduction

With the introduction of Excel 2013, Microsoft changed the windowing of Excel. On all previous versions, Excel had a so-called Multiple Document Interface (MDI), which means all open Excel files are hosted by one Excel application window. Excel 2013 introduced a Single Document Interface, meaning each workbook appears in its own Excel window.

This new feature of Excel causes some trouble with modeless userforms.

This article shows you how to resolve the issues involved.

Table of contents

  1. MDI vs SDI
  2. The code


Comments

All comments about this page:


Comment by: Dutch Gemini (28-2-2013 16:53:04) deeplink to this comment

Are you planning to add more stuff about broken compatibility or mishaps with Excel 2013? I have trouble altering the enabled state of CommandBarButtons on a CommandBar object that are not propagated to the "Add-Ins" tab of the Ribbon, the buttons remain disabled even though via VBA they are seen as enabled, so I can't execute any function.


Comment by: Jan Karel Pieterse (1-3-2013 15:30:11) deeplink to this comment

Hi Dutch Gemini,

If I encounter something and it hasn't been published, I guess I will.

Maybe it is time to start using RibbonX customisation? (though that will likely have it's own challenges with the new SDI interface)


Comment by: Dutch Gemini (4-3-2013 09:57:45) deeplink to this comment

Indeed I did, I have developed a RibbonX wrapper around the Add-In that hides the CommandBar and forwards clicks to this hidden CommandBar. In this way I have 1 [quite big] file containing the logic and a visual envelope for 2007+. My Add-Ins work perfect with Excel 2000 all the way up to 2010, and lot of customers still work with 2002/2003. As long as VBA cannot dynamically manage the RibbonX nor are there RibbonX Designers that manage also the CallBacks and the VB code natively, I will probably stick to this approach for some time.


Comment by: Paul Carlson (26-6-2013 22:33:54) deeplink to this comment

In Excel 2010, if I use 2 monitors and switch between the 2 monitors with multiple excel files open on both sides, it remembers which file was the last one I was using. In Excel 2013, the windows seem to ramdomly change order so that the last file I was looking at has now moved to the back. Do you know why and how to resolve. Thanks!


Comment by: Jan Karel Pieterse (27-6-2013 07:29:21) deeplink to this comment

Hi Paul,

Not sure what you mean by "Switch monitors"?


Comment by: Jon Peltier (20-9-2013 16:01:07) deeplink to this comment

Switching monitors isn't needed to see this. In the older Excel (up to 2010), Excel maintained an order of windows. If there were four Excel windows, you could ctrl-tab once to see another window, then you'd have to ctrl-tab three times to get back, or shift-ctrl-tab to go backwards. Every open window was cycled through.

In 2013, when you ctrl-tab once, Excel just seems to flip-flop the newly activated window and the newly deactivated window in the sequence. So ctrl-tab switches back without having to cycle through all the open windows. But then shift-ctrl-tab is unreliable.

And just now while trying this out, with three windows open, If I kept repeating ctrl-tab, the windows appeared in the order A-B-C-B-A-B-C-B-A, never switching directly between A and C.

I've had several cases in which I've been working on one worksheet, ctrl-tabbed to get to another to copy a cell, then shift-ctrl-tabbed back and pasted the cell, but I was not in the expected worksheet. So now I have to slow down and watch, to make sure it works correctly.


Comment by: Jan Karel Pieterse (20-9-2013 16:04:33) deeplink to this comment

Hi Jon,

I get it, highly irritating. Microsoft Expression Web works the same and if annoys me every time.


Comment by: Matt Russett (1-5-2014 19:00:47) deeplink to this comment

I just got 2013 and hate how they changed this Ctrl+Tab sequencing (among other things in 2013). Not only does the sequence appear to be random, it also cycles through other office applications like Outlook emails that are open and Word. It used to be that Ctrl+Tab stayed within the active applications and Alt+Tab switched between all applications.


Comment by: Jan Karel Pieterse (8-5-2014 07:27:47) deeplink to this comment

Hi Matt,

I agree, it is a PITA


Comment by: Keith LaChance (10-1-2016 09:29:04) deeplink to this comment

If anybody finds a way to change this random selection it would be great. I have many macro's and written a bunch of codes that require to know the next active window and this has been a nightmare to keep fixing the issues... One of my formula's takes two minutes to run and now I don't know if it is running under the correct files or not.


Comment by: Jan Karel Pieterse (11-1-2016 13:21:52) deeplink to this comment

Hi Keith,

This is an issue that many beginner VBA programmers run into: how to get from recorded macros to a properly built program.

When you record macros, the recorder records everything, including the selecting of objects. In VBA, selecting objects is hardly ever needed to make things work. In fact, it slows down the macro considerably. If you post a (small) sample of your code I can modify it to show what I mean.


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