BLOGS BY TOPIC
BLOGS BY AUTHOR
BLOGS BY YEAR
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
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:
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 & "."
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!
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
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!