If you omit buttons, the default value is null (the user must reply to the message box before they can continue working in the current application.) If you choose a button, you must set a variable to return a value, otherwise you will receive the following error message. Sets the system time to a specified time. This statement is not valid on UNIX™ or Macintosh OS X operating systems, for which you must have root user rights to change the system time. Searches for a template in a left-to-right string and returns a substring consisting of the characters in the string to the left of the template. Selects a set of statements to execute based on the value of an expression. In the example above, we have the OK and Cancel buttons in MsgBox. If you click the red cross in the top right, the return value is the same as the Cancel button. Robert Rosenberg runs his own consulting firm, specializing in customer-specific solutions and Microsoft Office training. His clients include five hundred companies in the entertainment, financial and legal sectors. As a Microsoft Valuable Professional in Excel, it also provides continuous and improved online support in Excel on behalf of Microsoft to users in their Internet newsgroups. Robert was responsible for updating the contents of the Excel and Office indexes for the 2002 version of this book.

This included updating code samples and collections for existing VBA objects and listing new object descriptions, their methods, properties, and/or arguments, and code samples. Next, we`ll use an If-Else to figure out what to do based on which button to press: The following constants can be used anywhere in the code instead of the actual values. Returns the current system date as a date/time value. Opens an OLE Automation object contained in an application file or returns the currently active OLE Automation object of the specified class. Tests an expression to determine whether it evaluates to a single value. 3) Carriage return + line feed – Chr(13)& Chr(10) or vbCrLf OR The message box returns vbYes or vbNo, depending on the user`s choice. You can then perform several actions depending on the selection: Specifies a variable that contains an 8-byte integer scaled to four decimal places to correctly represent a monetary value. Returns the file or directory names of a specified directory or drive volume name. Returns a string consisting of a specific character that repeats several times.

The character is specified as a string or value interpreted as a locale-dependent ASCII character code. Returns a date value for a specified set of year, month, and day numbers. The MsgBox function returns an integer value (between 1 and 7), so we define the variable as an integer type. However, instead of referencing the integer, you can refer to a constant (for example, vbOK, vbCancel, and so on). Check out this chart to see all the options: Stephen Bullen is based in Carlow, Ireland, and London, England. Since 1997, he has run his own company, Business Modelling Solutions Ltd, specialising in Excel development and consulting, and has worked for some of the world`s largest companies. The BMS website www.BMSLtd.co.uk contains a large number of examples of his work, including tools and utilities for extending the functionality of Excel and many examples of Excel development techniques. Stephen spends much of his free time supporting other Excel users by answering questions on the CompuServ Excel forum and Microsoft Internet newsgroup.

In recognition of his contribution and knowledge, Microsoft has awarded him Most Valuable Professional status every year since 1996. Stephen wrote most of the last chapters in Excel 2000 and Excel 2002 VBA. Programmer`s references, which have been incorporated into this book and updated by Paul Kimmel as appropriate. Stephen did not contribute directly to this issue. Transfers control to a list of labels, processes the instructions until a Return statement is reached, and returns control to the statement that responds directly to the On command. GoSub statement. The function and the MessageBox statement are identical, except that only the function has a return value. Excel VBA MsgBox is a dialog box that is used to prompt users to provide important information, usually providing the OK and Cancel buttons. Note that there is another dialog box called InputBox that receives text input from the user, but is not for the purposes of this article.

Buttons — Select the buttons you want to display. If not specified, OKonly. Here you can also specify the icon to display and the default button. 1) When you perform a sub or function, you only see the final result and the result may not be what you expect until any error message is displayed. In this case, you can insert Msgbox code into a procedure to check the value of a variable in a particular line of code. Returns a string that specifies the data type of the value of an expression. Returns an integer value indicating whether the end of a file has been reached. Valid values for the button, icon, default, and mode elements listed in the tables above are defined as constants in the LSCONST file.

LSS. If you want to use constants instead of numbers, include this file in your script. Concatenates all members of a string array and returns a string. The elements of the array are separated by a separator, if any, or by the space ( » » «. Converts all alphabetic characters in a string to uppercase and returns the resulting string. Specifies a variable that contains a double-precision floating-point value that is retained as an 8-byte floating-point value. A custom submarine that runs LotusScript when you delete an object that belongs to the class for which the Delete subobject® is defined. Returns the character represented by a value interpreted as locale-sensitive character code. The value returned by the MessageBox function is an integer between 1 and 7 inclusive. This value indicates the button that the user pressed in the message box, as shown in the following table. Conditionally executes one or more statements, based on the value of an expression, or passes control to a labeled statement.

Performs a search and replace routine on multiple values within an array. Returns the absolute value of a numeric expression. Returns the next integer value less than or equal to a number. In this case, you need to perform different actions depending on which key you press on it. Let`s take an example. This Excel tutorial explains how to use Excel VBA Msgbox with carriage return such as vbCr, vbNewLine, and handling return values such as vbOK, vbCancel, vbRetry. Tests the value of an expression to determine whether it is a custom object, a product object, or an OLE Automation object. Adds an array or scalar value to an array and returns the result as an array. Returns a time value for a specified hour, minute, and second. Specifies a variable with a value of True (-1) or False (0). Converts a numeric or string value to a date/time value. Displays a dialog box that prompts the user and returns user input as a string.

Displays a message in a message box and waits for confirmation from the user. The function form returns a value that corresponds to the button that the user presses. Removes leading spaces from a string and returns the result. John Green, Sydney, Australia, is an independent consultant specializing in Excel and Access. With 30 years of experience in computer science, a degree in chemical engineering and an MBA, he has a diverse background. He has conducted training courses on software applications and operating systems in Australia and overseas. Microsoft has awarded him «Most Valuable Professional» status every year since 1995. John was the lead author of the Excel 2000 and Excel 2002 VBA programmer references, which have been incorporated into this book and updated by Paul Kimmel as necessary.