Excel VBA Online Consulting

VBA for Excel Code for Message Boxes (MsgBox) and Input Boxes

Downloads

Tips on Excel

 

Tips on VBA for Excel

VBA for Excel Index and Search Tool (Click on the buttons below)

VBA Part 15 of 25: VBA for Excel Code for Message Boxes (MsgBox) and Input Boxes

In VBA for Excel the message box (MsaBox) 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"

Excel MsgBox

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 quotqtion marks within the message.

When a user clicks on a button in the Excel message box it triggers a 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 use a "Yes/No" message box.

When working with a "Yes/No" message box you will need to collect the answer given by the user.

Step 1: You first declare a variable to store the answer "Dim varAnswer as string".

Step 2: You store the answer in the variable with the code: "varAnswer = MsgBox("Are you sure that you want to delete all data?", vbYesNo, "Warning")". Notice the parentheses within which you submit the three arguments; the message, the type of message box and its title. Notice also the quotation marks surrounding the message and the title.

Step 3: You deal with the answer with an "IF" statement where if the user answers "No" you just exit the procedure with "Exit Sub"

Here is the whole procedure.

Sub proDangerousProcedure()

Dim varAnswer As String

    varAnswer = MsgBox("Are you sure that you want to delete all data?", vbYesNo, "Warning")
    If varAnswer = vbNo Then
        Exit Sub
    End if
    Here the dangerous procedure starts

End Sub

When you create a basic message box (first example above), you don't need to use parentheses. If you develop a more complex message box you will see that when you start writing the line of code (example above) varAnswer = MsgBox( right after the parenthesis Excel shows you what it is expecting as arguments:

1- The prompt is not optional, it is the message that you want your user to read in the message box and it must be between quotation marks.
2- When you enter the comma after the prompt Excel offers you a drop-down list of all the types of message box that you can create.

Excel message box types

Choose your message box and enter a comma.
3- The third argument is the title of the message box that will appear in the blue band at the top of the message box. Submit a title between quotation marks.

The fourth and fift arguments are rarely used, they are optional and you don't need to enter anything else...but the closing parenthesis.

As you can see there are many types of message boxes. The ones that are most often used are the vbYesNo, vbOKCancel, vbRetryCancel. You might also want to use a fancier message box like the vbQuestion, the vbInformation, the vbExclamation or the vbCritical as shown below.

Excel question message box MsgBoxexcel information MsgBoxExcel VBA Exclamation message boxExcel critical MsgBox

From the simple "OK" message box to the "Yes/No/Cancel" message box. From the simple message to complex ones using values of cells or variables. Hre how it is done.

There are no message box in VBA for Excel that you can show to the user while a procedure runs for a long time. But there is a way around this. Add a worksheet named "Wait" and insert a picture, a wordart or a message in very large font like "Procedure running" and hide the sheet.

Step 1: First you make the sheet that you have created visible and select it (Lines 1 and 2).
Step 2: You may want to change the cursor to the hour glass (Line 3)
Step 3: You deactivate the screen updating functionality (Line 4)
Step 4: You write your long procedure (Line 5 in the example)
Step 5: You hide the special sheet (Line 6)
Step 6: You bring back the default cursor (Line 7)
Step 7: You re-activate the screen updating functionality

Add this code to your long procedure. Just replace the line "Here is your long procedure" by your procedure.:

Sub proLongProcedure()

    Sheets("Wait").Visible=True
    Sheets("Wait").Select
    Application.Cursor = xlWait
    Application.ScreenUpdating=False
    
'Here is the long procedure
    Sheets("Wait").Visible=False
    Application.Cursor = xlDefault
    Application.ScreenUpdating=True

End Sub

VBA for Excel Code for Input Boxes

Input boxes are used to require a SINGLE value from the user.

input box

The basic code for an input box is as follow. With this 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 for the input box (like "Year" in this example

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

Excel Input box

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.

If you want to validate the value submitted by the user before proceeding with the procedure you will not store the value directly into a cells but in a variable. The code would then look like this:

Sub proInput()
Dim varInput As Integer

    varInput = InputBox("For what year do you need this report?")
    Sheets("Intro").Range("A1").value = varInput

End Sub

A variable "varInput" is declared Dim varInput As Integer to receive the value submitted by the user. The user enters a value that is transfered into a variable and then to a cell. Note here that the variable is declared as "Integer" so if the user submits a text "string" an error is generated. You can declare the variable as string Dim varInput As String if you are expecting a text or as date Dim varInput As Date if your are asking for a date.

With this approach you can use an error handler or some "IF" statement to validate the value submitted by the user before going on.

Here is the code to handle errors (canceled or invalid response from the user) and validate the value sumitted:

Sub proInput()
Dim varInput As Date

    On Error GoTo addError
    varInput = InputBox("For what date do you need this report?")

     If year(varInput)> 2006 then
        Msgbox "Invalid year"
        Exit Sub
      End If

     Do this and This

     Exit Sub

addError:

    MsgBox "This is not a date."

End Sub

In this procedure I first declare a variable of the date type Dim varInput As Date so that if the user submits anything else than a date there will be a VBA error to handle. I then tell VBA that if an oerro occur the procedure should jump to the address addError On Error GoTo addError. This means that if the user submits anything else tan a date the procedure will skip all the lines until it get to addError. A message box will then tell the user that what he has submitted is not a date MsgBox "This is not a date.". If what is submitted is indeed a date I use an "IF" statement to verify if the year of the date is greater than 2006. If it is there is another message box Msgbox "Invalid year" followed by an Exit Sub that puts an end to the procedure. If the value submitted is a date and the year is valid the procedure runs completely until it gets to the other Exit Sub just before the addError: ( with the colon at the end).

VBA for Excel Index and Search Tool (Click on the buttons below)

Comments: Click Here

VBA for Excel Code for Message Boxes (MsgBox) and Input Boxes

Excel VBA Online Consulting

Tips on Excel

 Downloads

Tips on VBA for Excel