Please Note: This article is written for users of the following Microsoft Excel versions: 2007, 2010, 2013, 2016, 2019, and Excel in Office 365. If you are using an earlier version (Excel 2003 or earlier), this tip may not work for you. For a version of this tip written specifically for earlier versions of Excel, click here: Specifying Location for a Message Box.
Leonard wonders if, when displaying a message box in a macro, there is a way to force the box to appear at a specific location on the screen.
There is no way to do this, as the MsgBox function doesn't include any way to specify a location. Instead, Excel displays the message box centered on the screen. If you need the capability to position the box, then the easiest solution is to rely upon the InputBox function. Unlike MsgBox, InputBox includes option parameters for specifying a location. Here's an example:
sName = InputBox(Prompt:="Enter your name", XPos:=2880, YPos:=1440)
Note that you can specify both an X position and a Y position for the upper-left corner of the box. The values assigned to these parameters are measured from the top-left corner of the screen, and are specified in twips. (There are 1440 twips to an inch.)
An input box does, of course, expect the user to provide input, whereas a message box does not. If you don't want to potentially confuse your users by soliciting input when none is really needed, then you'll need to create a UserForm to simulate a message box.
Note:
ExcelTips is your source for cost-effective Microsoft Excel training. This tip (10030) applies to Microsoft Excel 2007, 2010, 2013, 2016, 2019, and Excel in Office 365. You can find a version of this tip for the older menu interface of Excel here: Specifying Location for a Message Box.
Professional Development Guidance! Four world-class developers offer start-to-finish guidance for building powerful, robust, and secure applications with Excel. The authors show how to consistently make the right design decisions and make the most of Excel's powerful features. Check out Professional Excel Development today!
Need to know how many characters there are in a workbook? You can find out easily with the handy macro introduced in this ...
Discover MoreYou can use macros to process information in your worksheets. You may want to use that macro to apply the italic ...
Discover MoreAs your macro processes information in a worksheet, you may want to make sure that it skips over rows that are hidden. ...
Discover MoreFREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
2021-09-05 07:24:21
Jim Comin
Regarding location of message boxes, I was trying to figure out how to change the location, when thankfully I checked in with you and found that you can't.
You can, however, drag them around once opened. I had just created one that is right smack in the way of the users work, so I just added a notation in the message box that says, "you can drag it out of the way if it is interfering with your view."
2021-08-28 10:11:25
J. Woolley
My Excel Toolbox includes Sub MsgBoxCustom and Function MsgBox_Custom, which display a standard MsgBox with custom position and/or button labels. The custom position can be absolute screen coordinates or relative to a cell.
My Excel Toolbox also includes Function InputBoxVBA_Custom and Function InputBoxApp_Custom with screen coordinate or cell-relative positioning.
See https://sites.google.com/view/MyExcelToolbox/
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 © 2021 Sharon Parq Associates, Inc.
Please Note:
This article is written for users of the following Microsoft Excel versions: 2007, 2010, 2013, 2016, 2019, and Excel in Office 365. If you are using an earlier version (Excel 2003 or earlier), this tip may not work for you. For a version of this tip written specifically for earlier versions of Excel, click here: 
Comments