564 attributed reviews in the last 3 years
Refreshingly small course sizes
Outstandingly good courseware
Whizzy online classrooms
Wise Owls only (no freelancers)
Almost no cancellations
We have genuine integrity
We invoice after training
Review 30+ years of Wise Owl
View our top 100 clients
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.
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).
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.
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!
|Parts of this blog|
25 Aytoun Street