Userform loses focus after activating another workbook

Hi all,

I believe this is a Excel 2013 onwards issue with workbooks now opening on different windows. I've been trying a few ways to try and resolve this to no avail.

Basically this, I have a userform, which upon clicking, activates a separate workbook (which is already open). That workbook, upon activation, sits in front of the userform and the userform loses focus. I can't do anything on the userform until I click on the old workbook to reactivate.

Is there a way to keep userform in view with the background switching between the two workbooks?

I attach here a simple workbook to showcase this example. In it lies a button which creates a new workbook and shows a userform. The userform have 2 buttons. To showcase this issue, click on the one that says "Click Me!" which will switch workbook and lose focus of the userform. The other button closes the userform.

Macro that opens the userform (Module1)

Please Login or Register  to view this content.

Code in Userform1

Please Login or Register  to view this content.

Any thoughts / advice / help is much appreciated.

workbooks now opening on different windows...

The change from an MDI interface in XL 2010 to an SDI interface in 2013 makes this a bit of a problem.

Simplistically, an MDI (Multiple Document Interface) interface has all workbooks in the same 'physical' window but in an SDI (Single Document Interface) each workbook appears in its own window, but is still the same instance of Excel. Regardless if it's the same instance, the new Window is treated as a separate application so the userform stays 'docked' to the original window.

There's no real fix, only some workarounds and they all involve the use of APIs (AFAIK) to set the parent window of the userform to the active workbook - not sure of this is something you want to get into so info only to start with.

Yeah, I figured as much. That's pretty annoying.

Originally Posted by cytop

There's no real fix, only some workarounds and they all involve the use of APIs (AFAIK) to set the parent window of the userform to the active workbook - not sure of this is something you want to get into so info only to start with.

If you have a solution for this I would very much like to know. I tried something to try and keep the userform windows in front but I couldn't get it to work and causes it to crash from time to time.

Your sample file reworked...

The main changes is some API declarations in the userform and a reference to the Excel application what uses WithEvents to trap the Window_Activate event which is used to set the parent of the userform to the active window.

Thanks cytop! That works brilliantly. I'll try to make it work over the main project I was working on.

Do you have a good source of information for all these Windows API stuff?

Thanks cytop! Much appreciated!

I know that this thread is a few years old, but I could really use some help with the same issue.
The solution provided by cytop seems to be just what I need, but I'm having difficulty integrating it into my code.

My macro creates 6 new workbooks, then copies data from multiple sheets of ThisWorkbook and pastes over to Sheet1 of the newly created workbooks and saves them.

Example:
ThisWorkbook has 6 sheets: A, B, C, D, E, F
The code first adds 6 new workbooks, each one corresponding to one of the above 6 sheets.
A given range is copied from ThisWorkbook sheet A.
Copied data is pasted to Sheet1 of Workbook A.
Workbook A is saved.
The same is repeated sequentially for each of the other new workbooks (B-F).
Upon completion a message box informs the user that process is complete.

This procedure can take a quite a few seconds to complete, so I added a UserForm to display on screen throughout the duration of the processing.
I did this by simply adding "UserFormName.Show vbModeless" at the start of the subroutine. Then I have this at the end, just before the message box: "Unload UserFormName".
The UserForm ShowModal property is set to FALSE.

The problem is that the UserForm is immediately covered up by the added workbooks.

How can I keep the UserForm at the front during the entire procedure?

Originally Posted by FDibbins

Administrative Note:

Welcome to the forum.

We are happy to help, however whilst you feel your request is similar to this thread, experience has shown that things soon get confusing when answers refer to particular cells/ranges/sheets which are unique to your post and not relevant to the original.

Please see Forum Rule #4 about hijacking and start a new thread for your query.

If you are not familiar with how to start a new thread see the FAQ: How to start a new thread

Okay, will do. Thank you.

Most other forums that I've used advise to search for a thread about a particular topic to see if it already exists before creating a new post.

Is it permissible for me to use the exact title of this post for my new post? Because it is literally the same topic. I'm unsure how else to describe it.
I'm also concerned that doing so might be in violation of Forum rule #5.