Making Modal Dialog Boxes Appear in Front of Workbooks

by Allen Wyatt
(last updated January 3, 2015)

4

Don tends to work on two monitors and have multiple workbooks open on the same instance of Excel. He also maximizes his windows. (He describes himself as a "see everything in one shot" type of guy.) Recently, when Don has done something that opens a modal dialog box, the box opens behind the workbook, which stops him from seeing the dialog box or interacting with it. He wonders if there is a way he can get the modal dialog boxes to appear in front of the workbook.

Before answering, it should be pointed out that dialog boxes can be of two types: modal and modeless. A modeless dialog box is one that is virtually independent of the program to which it belongs. A good example of such a dialog box is the Find and Replace dialog box. On the other hand, a modal dialog box (the ones that Don is having problems with) must be responded to or closed before you can continue working with the program to which the dialog box belongs.

It is unclear whether Don's problem is with modal dialog boxes generated by Excel itself or if they are dialog boxes generated by macros over which Don has control. If it is the former, then there is very little that can be done; the layered location of the dialog box is controlled entirely by the program (and the programmers that created it). In other words, it would take a code change to make the dialog box appeared layered on top of the workbook instead of under it.

Understand that there is a scenario in which the problem may not be due to an Excel programming problem. It could be that you have a third-party application running on the system which affects the layering of dialog boxes and their parent windows. The typical culprits in this scenario are memory-resident utiltities that force themselves to always appear on top of whatever else is on the screen. The only way to see if this is the culprit is to disable the loading of such utilities and, within Excel, display a modal dialog box. If the behavior returns to normal, you then know the source of the problem.

If it is the case, however, that the dialog box is generated by a macro Don developed then the solution is to adjust the code that generates the modal dialog box. (In Excel macros these types of dialog boxes are typically implemented through userforms.) .

The problem with userform placement in multiple screen scenarios is overcome by the manual placement of the userform within its initiating code. For instance, you can use some variation on this:

Load UserForm1
UserForm1.StartUpPosition = 0
UserForm1.Top = Application.Top + 25
UserForm1.Left = Application.Left + 25
UserForm1.Show

You may need to experiment with the placement, but setting the StartUpPosition property to 0 is required so that VBA knows you want to manually location the userform.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (13349) applies to Microsoft Excel 2007, 2010, and 2013.

Author Bio

Allen Wyatt

With more than 50 non-fiction books and numerous magazine articles to his credit, Allen Wyatt is an internationally recognized author. He  is president of Sharon Parq Associates, a computer and publishing services company. ...

MORE FROM ALLEN

Ordering Search and Replace

The wildcard searching available in Word is very powerful. Here's how you can use ordering in your search efforts to make ...

Discover More

Moving from Sheet to Sheet

Need to move quickly through the worksheets in a workbook? Learn the keyboard shortcuts and you can make short work of this ...

Discover More

Adding a Hidden Tool in Word

Word no longer includes a Hidden tool on the ribbon as it used to in earlier versions that used toolbars. You can still add ...

Discover More

Create Custom Apps with VBA! Discover how to extend the capabilities of Office 2013 (Word, Excel, PowerPoint, Outlook, and Access) with VBA programming, using it for writing macros, automating Office applications, and creating custom applications. Check out Mastering VBA for Office 2013 today!

More ExcelTips (ribbon)

Understanding Functions in Macros

Functions are a common programming construct. They help you to create easy ways of processing information and returning a ...

Discover More

Swapping Two Numbers

When programming macros, variables are used extensively. At some point you might want to exchange the values held by two ...

Discover More

Opening a Workbook and Suppressing Automatic Macros

Want to stop Excel from running any automatic macros that may be stored with a workbook? Here's how to do it.

Discover More
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

View most recent newsletter.

Comments

If you would like to add an image to your comment (not an avatar, but an image to help in making the point of your comment), include the characters [{fig}] in your comment text. You’ll be prompted to upload your image when you submit the comment. Maximum image size is 8Mpixels. Images larger than 600px wide or 1000px tall will be reduced. Up to three images may be included in a comment. All images are subject to review. Commenting privileges may be curtailed if inappropriate images are posted.

What is 7 - 0?

2015-01-05 11:08:53

Don Chartier

Allen -- thanks! I'm not writing macros, but will try your tip re 3rd-party apps.

Thanks again for your help.


2015-01-03 23:32:10

MF

@Bob, assume you have only one hyphen, you may try the following formula:
=REPLACE(A1,FIND("-",A1)+1,1,UPPER(MID(A1,FIND("-",A1)+1,1)))


2015-01-03 12:03:18

Bob Lilly

The problem I have is when a name is hyphenated (-) the next letter is not capitalized. The following is an example of the problem and the solution I would like to achieve.
Marie Kelly-jones
Marie Kelly-Jones.


2015-01-03 07:56:29

sheldon hopkins

I'm not familiar with modal or non-modal boxes, but I am familiar with boxes that are placed in "weird" places. I use two, three, and sometimes four monitor systems as a normal situation. Boxes seem to appear with random position. For the most part they seem to prefer the Primary Monitor, either on top of,or under another window already on the monitor. And centered in the three monitor system is another choice. If hidden the task bar seems to be the best place to 'find' the box. Excel/VBA does not appear to be aware of multiple monitors.


This Site

Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.

Newest Tips
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

(Your e-mail address is not shared with anyone, ever.)

View the most recent newsletter.