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
Comments
Showing last 8 comments of 11 in total (Show All Comments):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.