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.

  1. Using MsgBox to Display Messages in VBA Macros
  2. Displaying Message Boxes
  3. Joining Bits of a Message Together (this blog)
  4. Customising your Message Box
  5. 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

You need a minimum screen resolution of about 700 pixels width to see our blogs. This is because they contain diagrams and tables which would not be viewable easily on a mobile phone or small laptop. Please use a larger tablet, notebook or desktop computer, or change your screen resolution settings.

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:

MsgBox showing concatenation

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:

Displaying worksheet names

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!

This blog has 0 threads Add post