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.2 - Using Multiple Lines

When you have a long message to display, you might want to break it into multiple lines. This page describes how to add multiple lines of text to a single message box.

We probably don't need quite so many lines for this message.

Files Needed

You don't need any files for this section.

Completed Code

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

You can insert a new line character into a message box prompt by using the vbNewLine constant. Using any workbook, insert a new module and create a subroutine called UsingMultipleLines. Add an instruction to display a message box as shown below:

Concatenate the vbNewLine constant between the lines of your message.

Running the code shown above will produce a message box with two lines of text:

The exact text that appears depends on the date and name of the workbook in which your code is stored.

You may see other people using an older technique to achieve the same result. You can use vbCrLf in place of vbNewLine to create a new line. Create a new subroutine or edit the existing one to demonstrate this:

This code produces the same result as the easier-to-understand vbNewLine.

vbCrLf stands for Visual Basic Carriage-Return Line-Feed.

Yet another technique you may see involves concatenating the individual characters for a carriage-return and line-feed:

The result of running this code is the same as the previous two examples.

If you're curious about the history of new line characters (and who isn't?!), Wikipedia has articles on Control Characters and Teleprinters. Enjoy!

To practise using variables and new lines with a message box:

Using the workbook you created earlier, add a new subroutine called UsingVariablesAndNewLines.
Write code to declare a string variable and assign a value to it:

Sub UsingVariablesAndNewLines()

'declare a variable

Dim MsgText As String

'assign a string to the variable

MsgText = "Hello " & Environ("UserName")

End Sub

Add more code to pass the variable to a message box:

Sub UsingVariablesAndNewLines()

'declare a variable

Dim MsgText As String

'assign a string to the variable

MsgText = "Hello " & Environ("UserName")

'display the variable's value

MsgBox MsgText

End Sub

Run the subroutine then click OK to return to the code.
Modify the code to add an extra line in the message text:

Sub UsingVariablesAndNewLines()

'declare a variable

Dim MsgText As String

'assign a string to the variable

MsgText = "Hello " & Environ("UserName")

MsgText = MsgText & vbNewLine

MsgText = MsgText & "Welcome to " & ThisWorkbook.Name

'display the variable's value

MsgBox MsgText

End Sub

Run the subroutine to check that it works.

The exact message will depend on your user name and the file name.

Save and close the workbook.

This page has 0 threads

Add a new post

MODULES

LESSONS

TOPICS

Files Needed

Completed Code

Head office

London

Manchester