This page has 0 threads | Add post

Blogs

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

Using MsgBox to Display Messages in VBA Macros Part three of a five-part series of blogs
The MsgBox command in Visual Basic for Applications allows you to display basic messages, but you can also make it prettier, and even use it to ask questions or display multiple-line messages! Read on to find out more about this versatile VBA command. Using MsgBox to Display Messages in VBA Macros Displaying Message Boxes Joining Bits of a Message Together (this blog) Customising your Message Box Using MsgBox to Ask Questions This blog is part of our Excel macros online tutorial series. Our main business is running training courses in Excel, courses in VBA and training in many other Microsoft applications.

Using MsgBox to Display Messages in VBA Macros

Part three of a five-part series of blogs

The MsgBox command in Visual Basic for Applications allows you to display basic messages, but you can also make it prettier, and even use it to ask questions or display multiple-line messages! Read on to find out more about this versatile VBA command.

Using MsgBox to Display Messages in VBA Macros
Displaying Message Boxes
Joining Bits of a Message Together (this blog)
Customising your Message Box
Using MsgBox to Ask Questions

This blog is part of our Excel macros online tutorial series. Our main business is running training courses in Excel, courses in VBA and training in many other Microsoft applications.

Posted by Andy Brown on 28 September 2011

Joining Bits of a Message Together

Often you will want to create a message to be displayed on screen by joining bits of text together. You can do this using the & symbol (which is pronounced Ampersand).

Example One - a Greeting System

The following example uses two simple input boxes (covered in a separate blog) to ask a user what their name and preference is, then redisplays the choices made in a message box:

Sub ShowName()

Dim YourName As String

Dim YourBag As String

YourName = InputBox("What is your name?", "Choose name")

YourBag = InputBox("What bag are you into?", "Choose bag")

MsgBox "Hello, " & YourName & ". Good to hear you're into " & YourBag & "."

End Sub

Here's what you see if you choose Bob and Sheep in reply to the two questions:

Every time you join two bits of text together, you need an ampersand (&). Here we have no less than 5 bits of text we're joining together: the word Hello, the name of the person, the middle bit of text, the person's preference and a trailing full stop - so we need 4 ampersands.

Example Two - Displaying Worksheet Names

The following code displays a list of all of the worksheet names in the active workbook.

Note the use of VbCrLf - standing for Visual basic Carriage return Line feed - to insert a line break. Older programmers might like to know that this represents the ASCII characters 13 and 10 joined together!

Sub ShowSheetNames()

Dim i As Integer

Dim msg As String

'initialise message string

msg = "This file has the following worksheets:" & vbCrLf

'loop over worksheets, showing names

For i = 1 To Sheets.Count

msg = msg & vbCrLf & Sheets(i).Name

Next i

'show results

MsgBox msg

End Sub

You can also use vbNewLine to throw a new line, which is easier to type and remember.

Here's what you might get if you ran this macro:

Notice that each worksheet name is on a different line of the message.

Now that we've learnt how to display plain messages, let's see if we can tart them up a bit!

Parts of this blog
Using MsgBox to Display Messages in VBA Macros Displaying Message Boxes Joining Bits of a Message Together (this blog) Customising your Message Box Using MsgBox to Ask Questions

Some other pages relevant to the above blogs include:

This blog has 0 threads

Add a new post

Book any course as classroom, onsite or online training, or as one-to-one consultancy

Free resources

Paid services

Getting help

Where we are

Where you are

Online

Transparent reviews

Delivery of courses

Nicer to work with

Reliable and established

Our training courses

C#

Excel

MySQL

Power Apps

Power Automate

Power BI and DAX

Python

Report Builder

SQL

SQL Server

VBA Macros

Other training resources

Blogs

Exercises

YouTube tutorials

YouTube shorts

Test your skills

Consultancy

Courseware

Self-paced courses

Newsletters

Publications

Our training venues

London

Manchester

Across the UK

At your site

Online

Why we are different

Hundreds of published reviews

Small class sizes

Outstanding courseware

Online classroom

(Almost) no cancellations

Wise Owl trainers only

Genuine integrity

Invoices after training

30+ years of Wise Owl

Our top 100 clients

BLOGS BY TOPIC

BLOGS BY AUTHOR

Joining Bits of a Message Together

Example One - a Greeting System

Example Two - Displaying Worksheet Names

Head office

London

Manchester