Excel Visual Basic for Applications online training

This page has 0 threads | Add post

Finding our tutorials useful? Please consider helping us to share more!

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.

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:

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:

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:

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

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

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:

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:

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:

Using any workbook (perhaps create a new, blank one), open the VBE and insert a new module.
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

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

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

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

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

To practise displaying values on a message box:

Using any workbook, create a new subroutine called ShowingOtherValues.
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

Run the subroutine then click OK to return to the code.
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

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

Run the subroutine to check that you see each message.
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

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

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 a new post

MODULES

LESSONS

TOPICS

Files Needed

Completed Code

Displaying Properties

Displaying Function Results

Displaying the Value of a Variable

Head office

London

Manchester