How to use the MessageBox (MsgBox)

Message boxes are used for events to provide guidance, to warn about an error, to give confirmation, for a description, to oblige a choice, and for multiple other things.

The Excel MsgBox function takes advantage of a library in the operating system (Windows) inserted by the designers of the OS to tell us about the systems errors. In Excel, through VBA, you can call this library and  “command” the appearance of messages with the appropriate instructions.

The simplest, is this:

MsgBox “Hello!”

The syntax to use is:

MsgBox (Prompt [, buttons] [, title] [, HelpFile, context])

For a complete explanation of the predefined arguments can be used the online help. The argument prompt indicates that the message has to be displayed; While below are reported the constants of the argument  “buttons ” which are those that allow us to choose the “type” of MsgBox: from the simplest (with an OK button) to those with 2 or 3 buttons. The arguments are optional, ie you can not insert them.

 

When we use a constant the syntax will be for example:

MsgBox (“Hello!”, vbInformation)

But if we use this statement the debugger reports an error: You must assign the statement to a variable, String or Variant:

Say=MsgBox (“Hello!”, vbInformation)

The length of a message can reach up to about 1024 characters, depends on the font used. The fonts normally used, do not occupy all the same space; In a text string, the character  “i “, for example, occupies less space than a  “A” or a “G”, as well as a lowercase character occupies less space than the uppercase. The only font that maintains the same space for each letter, is the Courier. If the message is split across multiple lines, you can include, between each pair of rows, a carriage returns (CHR (13)), a line feed character (Chr (10)), or a carriage return-linefeed sequence (Chr (13) & Chr (10)). The characters just exposed, can be replaced by the corresponding constants, as reported below:

vbCr       can replace  Chr(13)

vbLf       can replace  Chr(10)

vbCrLf   can replace  Chr(13) + Chr(10)

This below is an example on how you can compile code to bring up a message on multiple lines, when opening the workbook:

Sub Message()
Dim Lem As String
Lem = Lem & “This one is an example :” & vbLf & vbLf
Lem = Lem & “The author is:” & Chr(13) & vbLf
Lem = Lem & “Mike” & vbLf
Lem = Lem & “This Programm” & vbLf
Lem = Lem & “is protected by” & vbLf & vbLf
Lem = Lem & “copyrights !!” & vbLf
Lem = Lem & “(That’s not a true)” & vbLf & vbLf
Lem = Lem & “of this VBa code of this message” & vbLf
Lem = Lem & “you can change has you want ” & vbLf
Lem = Lem & “, you can add what you want”
MsgBox Lem
End Sub

Below the message created by the code reported above:

Now below is reported some example with vbYesNo and vbYesNoCancel:

***Example 1 ***

Sub Test_1()
mess = MsgBox(“Hello! Do you want continue ?”, vbYesNo)
If mess = vbYes Then
MsgBox “Test”

End If
End Sub

 

Note, in this example, the closing X is disabled: you can only carry on with a “Yes” or a “No”. For the execution of the code, an operative choice has been inserted; according to the button that the user will choose, we can introduce instructions for one or two options. In this example, answering yes, we get a result, answering no, we get to not change the result.

 

***Example 2 ***

Sub Test_2()
mess = MsgBox(“Hello! Do you want continue?”, vbYesNo)
If mess = vbYes Then
MsgBox ” Test 2″
Else

MsgBox “Hello!”

End If

End Sub

In this example if you do not answer “Yes”, with “No” the message is “Hello!”

 

***Example 3 ***

In this example, we get the window with vbYesNoCancel, in which the Cancel button would allow us to exit without having chosen. However, you can also use the Cancel button (or the closing X) to get a third possible action, other than closure, which will force a third alternative. This is an example:

Sub Test_3()
mess = MsgBox(“Hello! Do you want continue?”, vbYesNoCancel)
If mess = vbYes Then
MsgBox “Hello”
ElseIf pippo = vbCancel Then
MsgBox “Wow !!!”
Else
MsgBox “Bye !!!”
End If
End Sub

As you can see, all three buttons are used, to obtain three obligatory alternatives, excluding an exit without choices.

***Example 4 ***

This example is like Sub Test_3 () but we use “Select Case” instead of the construct “if…… Else”, thus:


Sub Test_4()
Dim iRisposta As Integer
iReply = MsgBox(“Save file?”, vbYesNoCancel)
Select Case iReply               ‘set “Select Case” referred to the message given by iReply variable
Case vbYes                           ‘if we reply with “Cancel”:
ThisWorkbook.Save             ‘save the file and
Application.Quit                  ‘close the file
Case vbCancel                     ‘if we reply with “Cancel”:
Exit Sub                                ‘Exit from the routine
Case vbNo                           ‘if we reply with “No”:
Application.Quit                  ‘close the file without to save
Case Else
End Select
End Sub

This code-example has different syntax, that use vbYesNo, but in this case there is vbno as defaults to not perform an action. 

 


This entry was posted in Excel & VBA tips and tagged , , , , , , , , , , , , , , , . Bookmark the permalink.

Leave a Reply