Module 5 - Messages and User Inputs
Lesson 5.1 - Displaying Messages
Topic 5.1.1 - Displaying a Simple Message

Displaying a message box is a useful way to give your users information or instructions.  This page describes how to display simple messages to the user.

Basic message

We'll start by looking at how to show a simple message like this one.

 

Files Needed

You don't need any files for this page.

Completed Code

You can click here to download a file containing the sample code.

In any workbook, insert a new module in the VBE and create a subroutine called SimpleMessage. You can display a message to the user with the MsgBox function.  You can see the list of parameters for the MsgBox function in the image below:

The tooltip appears when you type MsgBox followed by a space.

Although the MsgBox function has five parameters, you only need to pass a value to the first one, Prompt, in order to make it work.  The code shown in the image below will make a simple message appear on screen:

Simple message

Enclose your message in a set of double-quotes.  Hopefully you can think of something more inspirational than the example shown here.

 

Although the tooltip shows the MsgBox parameters contained in parentheses (round brackets), you shouldn't actually type these in when you simply want to display a message on screen.

Executing the code shown above results in a basic message box appearing to the user, as shown below:

Message on screen

The exact appearance of the message box depends on the theme you have applied to Windows.

 

When the message box appears, your code will pause.  When you click OK (or click the X in the top right corner) on the message box, your code will resume.

Displaying Properties

You can refer to a property of an object in the prompt of a message box.  Create a subroutine called ShowAProperty and add code which displays the Name property of the workbook in a message box:

Show property

This code reads the name of the workbook into the message box.

 

Running the code shown above produces a message box similar to this:

Message box

The exact result will depend on the name of the workbook in which your code is stored.

 

Displaying Function Results

You can return the result of a function to a message box and concatenate this with other information to build a message.  Create a new subroutine called ShowAFunction and add code to display the current date as shown below:

Concatenate

This example concatenates a string with the result of the Date function.

 

Displaying the Value of a Variable

If you have a particularly long message to display, you may prefer to store it in a variable before passing it to the message box.  The code shown below concatenates a message and stores it in a variable.  The message box refers to the variable in order to display its contents:

Using variable

Storing the message text in a variable makes it much easier to write the code to display the message box.  If you aren't working in Microsoft Windows you may wish to replace Environ("UserName") with Application.UserName for this example.

 

To practise displaying simple message boxes:

  1. Using any workbook (perhaps create a new, blank one), open the VBE and insert a new module.
  2. Create a new subroutine called MoreMessages and add the code shown below (if you can't think of anything profound or intelligent to say, you could always go with the traditional Hello, world!):

Sub MoreMessages()

 

'show a simple message

MsgBox "The only thing I cannot resist is temptation."

 

End Sub

  1. Click within the subroutine and press F5 to run it.  Check that your message box appears on screen.
Message appears

You should see something similar to this when you run the subroutine.

 
  1. Click OK to close the message then try adding a second message box in the same subroutine:

Sub MoreMessages()

 

'show a simple message

MsgBox "The only thing I cannot resist is temptation."

 

'show another message

MsgBox "Quotation is a serviceable substitute for wit."

 

End Sub

  1. Run the subroutine to see each message appear, one after the other.
  2. Save and close the workbook.

To practise displaying values on a message box:

  1. Using any workbook, create a new subroutine called ShowingOtherValues.
  2. Write code to display a message box which shows the name of the active worksheet:

Sub ShowingOtherValues()

 

'show the name of the active sheet

MsgBox ActiveSheet.Name

 

End Sub

  1. Run the subroutine then click OK to return to the code.
  2. Modify the code to add some literal text:

Sub ShowingOtherValues()

 

'show the name of the active sheet

MsgBox "You're looking at " & ActiveSheet.Name

 

End Sub

  1. Add a second message box which displays the time and date:

Sub ShowingOtherValues()

 

'show the name of the active sheet

MsgBox "You're looking at " & ActiveSheet.Name

 

'display the time and date

MsgBox "It is " & Time & " on " & Date

 

End Sub

  1. Run the subroutine to check that you see each message.
  2. Declare a String variable called MsgText:

Sub ShowingOtherValues()

 

Dim MsgTxt As String

 

'show the name of the active sheet

MsgBox "You're looking at " & ActiveSheet.Name

 

'display the time and date

MsgBox "It is " & Time & " on " & Date

 

End Sub

  1. Use the variable to store the text displayed on the first message box and then pass the variable to the prompt of the message box:

Sub ShowingOtherValues()

 

Dim MsgTxt As String

 

'show the name of the active sheet

MsgTxt = "You're looking at " & ActiveSheet.Name

 

MsgBox MsgTxt

 

'display the time and date

MsgBox "It is " & Time & " on " & Date

 

End Sub

  1. After the first message box, use the MsgTxt variable to store the text displayed on the second message box.  Pass the variable to the prompt of the second message box:

Sub ShowingOtherValues()

 

Dim MsgTxt As String

 

'show the name of the active sheet

MsgTxt = "You're looking at " & ActiveSheet.Name

 

MsgBox MsgTxt

 

'display the time and date

MsgTxt = "It is " & Time & " on " & Date

 

MsgBox MsgTxt

 

End Sub

 

This page has 0 threads Add post