613-749-4695 (Peter)
[email protected]

Home Page

Tips and Ideas on Excel
Table of Contents on Excel

Tips and Ideas on Macros
Table of Contents on VBA

Quick Links

VBA Chapter 16 of 24: Message Boxes (MsgBox) and Input Boxes in Excel

VBA for Excel Code for Message  Boxes MsgBox

In VBA for Excel the message box (MsgBox) is the primary tool to interact with the user. You can use it to inform, alert him or ask him to choose a certain path (Yes/No).

The code in VBA for Excel to generate the following basic message box is:
MsgBox "Your message here"

When you develop a procedure that takes time to run, you end your code with a basic message box telling the user that the procedure has been executed.
MsgBox "The report is ready"
or
MsgBox "The procedure has been executed you may now go to sheet "" Cover" ""
or
MsgBox "The procedure has been executed you may now go to cell ""A1"""

Notice that if you want part of your message to be between quotation marks you have to double the quotation marks within the message.

When you develop a VBA procedure that will do something important like delete all data always start the code with a message box that asks the user if he is certain that he wants the procedure to run. In this case you will use a "Yes/No" message box.

Excel VBA Input Boxes

Input boxes are used to require a SINGLE value from the user but it has many limits that will drive you to use VBA userforms instead.

VBA for Excel Code for Input Boxes

The basic code for an input box is as follow. With the following procedure whatever value submitted by the user is entered in cell "A1" of a sheet named "Intro" .
Sub proInput()
        Sheets("Intro").Range("A1").Value = InputBox("For what year do you need this report?")
End Sub

You can also use a different header  instead of "Microsoft Excel" for the input box like "Please answer this question." in this example:

Sheets("Intro").Range("A1").Value = InputBox("For what year do you need this report?" , "Please answer this question.")

Before we go on I must tell you that it is not possible to impose the password character (*) in the input box. To achieve this you must work with a userform and a text box. See how in the section on userforms and controls

See 20 message boxes with dynamic messagesincluding: the vbQuestion, the vbInformation, the vbExclamation or the vbCritical message boxes and how it is done in "vba-tutorial-message-boxes.xls"

613-749-4695 (Peter)
[email protected]

Home Page

Tips and Ideas on Excel
Table of Contents on Excel

Tips and Ideas on Macros
Table of Contents on VBA

Quick Links