Written by Allen Wyatt (last updated May 23, 2020)
This tip applies to Excel 2007, 2010, 2013, 2016, 2019, and Excel in Microsoft 365
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 utilities 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.
Note:
ExcelTips is your source for cost-effective Microsoft Excel training. This tip (13349) applies to Microsoft Excel 2007, 2010, 2013, 2016, 2019, and Excel in Microsoft 365.
Program Successfully in Excel! John Walkenbach's name is synonymous with excellence in deciphering complex technical topics. With this comprehensive guide, "Mr. Spreadsheet" shows how to maximize your Excel experience using professional spreadsheet application development tips from his own personal bookshelf. Check out Excel 2013 Power Programming with VBA today!
When you open multiple workbooks, the way in which Excel sizes them is not the best for your needs. This tip looks at a ...
Discover MoreThe macro programming language used in Excel gives you a great many tools that allow you to modify the way that Excel ...
Discover MoreMacros can be used to change the formatting of your worksheet, if desired. One change you might want to make is to the ...
Discover MoreFREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
2020-09-23 02:57:02
Mike Fairley
Thanks J Woollley. They are all great suggestions, I will try them out and let you know how it works. I am learning a lot!
BTW - We have been struggling with this for more than a year for a large client. The dialog behaviour is very unpredictable. On some devices/version of Excel it is fine, and others not. I have a computer where it *always* goes to the back.
2020-09-22 15:59:28
J. Woolley
@Mike Fairley
I believe my previous suggestion will not work for this case. When I test your code with my configuration (after adjusting your oApp/olApp variables), the address book always appears in the foreground. I even tested calling your code from a UserForm, which you implied in your original question. Since the address book does not appear in the foreground for your configuration, try adding the following lines before and after the With... section:
Application.WindowState = xlMinimized
With oDialog
...
End With
Application.WindowState = xlNormal
If your code is called from a UserForm, try changing the form's ShowModal property to False. If that fails, try calling your code from a standard module and passing the results to your form's code.
If you cannot resolve the issue, this would be a good question for wellsr VBA Q&A: https://ask.wellsr.com/vba/
You might also be interested in My Excel Toolbox: https://sites.google.com/view/MyExcelToolbox/
2020-09-22 06:28:29
Mike Fairley
Thanks J Wooley, appreciate your suggestion. I am not sure where to put your code.
Sub AddressBookName()
Dim oApp As Outlook.Application
Dim oDialog As SelectNamesDialog
Dim oGAL As AddressList
Dim myAddrEntry As AddressEntry
Dim exchUser As Outlook.ExchangeUser
Dim AliasName As String
Dim FirstName As String
Dim LastName As String
Dim EmailAddress As String
Set oApp = CreateObject("Outlook.Application")
Application.Wait (Now + TimeValue("0:00:05"))
Set oDialog = olApp.Session.GetSelectNamesDialog
Set oGAL = olApp.GetNamespace("MAPI").AddressLists("Global Address List")
With oDialog
.AllowMultipleSelection = False
.InitialAddressList = oGAL
.ShowOnlyInitialAddressList = True
If .Display Then
--------------'this is the point where the Outlook dialog is displayed ---------------
AliasName = oDialog.Recipients.Item(1).Name
Set myAddrEntry = oGAL.AddressEntries(AliasName)
Set exchUser = myAddrEntry.GetExchangeUser
If Not exchUser Is Nothing Then
FirstName = exchUser.FirstName
LastName = exchUser.LastName
EmailAddress = exchUser.PrimarySmtpAddress
End If
End If
End With
Set olApp = Nothing
Set oDialog = Nothing
Set oGAL = Nothing
Set myAddrEntry = Nothing
Set exchUser = Nothing
End Sub
2020-09-21 10:41:15
J. Woolley
@Mike Fairley
You can try adding this line to your VBA after opening OAB:
CreateObject("WScript.Shell").AppActivate sOAB
where sOAB is a String identifying all or part of the OAB's titlebar caption. For example:
sOAB = "Address Book"
2020-09-20 22:13:06
Mike Fairley
Hi Allen. Love your work. Your site is my "goto" for Excel queries.
I have a related issue. When I call the Outlook Address Book from an Excel VBA form. The OAB always goes behind the Excel application. Any ideas how to resolve??
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.
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
Copyright © 2024 Sharon Parq Associates, Inc.
Comments