Modal userform prevents use of other workbooks-even in new application instance (1 Viewer)

Isaac

Lifelong Learner
Local time
Today, 06:21
Joined
Mar 14, 2017
Messages
2,769
When an Excel VBA userform is displayed modally, that prevents a user from interacting with other things on the Excel screen - cells, other sheets, file / ribbon, etc.
Even, it will inhibit--but not totally prevent, performing most actions in a separate workbook, which is open within the same Excel application instance.

However, I feel certain that I remember in the past, this problem basically was overcome by simply opening the "2nd" workbook (the one without the userform displayed, that you want to work in WHILE the other workbook's userform is displayed), in a separate Excel instance. For certain, I know this would achieve being able to work with a 2nd workbook while code is running in the 1st workbook.

Now I can no longer do that, am using Office 365. I have one workbook with a modal userform displayed, and I open a brand-new 2nd instance of the Excel application. From there I want to go File > Open (the other workbook), but I find I can't even click on File!

I don't think this was always the case, and certainly is not the case with a Microsoft Access form displayed popup & modal (there you just happily open a new Access app instance and can do anything needed). Am I missing anything else that needs to be done or is this just one more awful thing about Office 365?
 

The_Doc_Man

Immoderate Moderator, Former MVP, Retired SysAdmin
Staff member
Local time
Today, 08:21
Joined
Feb 28, 2001
Messages
18,364
A "modal" dialog interacts with the O/S by locking the status of the process that holds the screen. This interaction means that the scheduler cannot put a new process in place because the CPU resource is locked up exclusively for the thing triggering the modal dialog.

 

Isaac

Lifelong Learner
Local time
Today, 06:21
Joined
Mar 14, 2017
Messages
2,769
I understand the basic concept of it of course, but it seems like in the past you could get around this by opening a new application instance and open a second workbook from there. You can do it in Access for example.
In some applications it is, and I wish it always was, limited to the scope of that application instance..
 

Isaac

Lifelong Learner
Local time
Today, 06:21
Joined
Mar 14, 2017
Messages
2,769
Had a user complain about this today. "Why can't I use another excel workbook, while I have yours open (with a userform modally displayed).
They said, we always used to be able to do this, IF we opened the 2nd workbook in a 2nd Excel app instance, but now even though we're doing that, it still isn't use-able. I expressed the same sentiment--used to work, doesn't in 365.

Then I wrote some code to force 2 different files to open in 2 different Excel instances. And lo and behold - everything worked GREAT. I could display a modal userform in one workbook, and still totally work in the other workbook.

This made me realize that the problem was, we really hadn't been effectively creating a 2nd Excel app instance like we'd thought we were.

Researched that and found this article. Now that I know how to actually force a 2nd Excel app instance, (manually), in Office 365, I no longer have this problem. Just in case anyone stumbles across this same frustration .... in 365, Start Menu > Excel > Enter no longer creates a new app instance, ya have to follow those article steps.
 

Darrell

Registered User.
Local time
Today, 14:21
Joined
Feb 1, 2001
Messages
211
Interesting - I've been using this technique to start a new Excel instance for almost a year now as I found that if I opened a spreadsheet to run a macro and I already had other sheets open with their own macros, even though they weren't being referenced, the macro I wanted to run would run up to 10x slower!
 

Isaac

Lifelong Learner
Local time
Today, 06:21
Joined
Mar 14, 2017
Messages
2,769
You mean the technique I referenced in that article?
 

Darrell

Registered User.
Local time
Today, 14:21
Joined
Feb 1, 2001
Messages
211
Sorry - yes the one referenced. Has proven a saviour from much frustration.
 

Isaac

Lifelong Learner
Local time
Today, 06:21
Joined
Mar 14, 2017
Messages
2,769
Yeah I was not only happy to discover that method which I didn't know about until recently, but also very happy to confirm that the general principle of a modal user form in Excel not preventing work as long as you're in a different Excel instance, still being true!
 

Users who are viewing this thread

Top Bottom