Create a Message Box with VBA : MsgBox

Using VBA you can create a message box informing the user of something or asking him to perform something.

The Message Box is a dialog box create in Excel VBA and it looks like all the message boxes you have ever seen. For example here.

message box

This message box was create by creating in your macro the following Visual Basic code

MsgBox "Hello this is a message box"



A critical message can be create with the following code. A VBA critical message will include the critical logo.

critical message

MsgBox "Hello this is a message box", vbCritical, "this is a title"

 

To enter a longer message like on 2 lines, enter the following code:

MsgBox "First Line" & vbNewLine & "Second Line"

2 line message box

 

A message displaying a value, for example the value of a cell like "A1"

MsgBox "welcome to Excel Made Easy" & vbNewLine & "the value of cell A1 is " & Range("A1"), vbOKCancel, "This is the title of the message box"

You can see we used the attribute vbOKCancel to tell him it must contain the 2 buttons OK and Cancel. The next part was the title or header of the message box.

 

message box with value

 

You can select different type of message boxes. For example with just OK button, or OK and Cancel or Yes and No

For example this line will open the following message box

MsgBox "Do you want to marry me me", vbYesNoCancel + vbQuestions, "the big Question"

vbquestions msgbox

When working in Visual Basic Editor you will see how he provides you with the possible answers. Here there many configuration possible for the buttons configuration.

msgbox buttons



 

So you can have something like this

MsgBox "Do you want to marry me", vbYesNoCancel + vbDefaultButton1 + vbCritical

vbyesnocancel

 

To such a question (do you want to marry me?), you have to answer and especially you have to react on your sheet. So here how to:

Therefore you have to use the answer of the user. This can be done in VBA like the following code.

Sub Messagebox_question()

Dim response As Integer

response = MsgBox("Do you want to marry me", vbYesNo + vbQuestion, "The big question")
'this puts the value of the answer in the variable response'

If response = vbYes Then
'we test the response if it is a YES or a NO and write the value in cell A35

Range("a35").Value = "Oh my Love, I will be yours for ever"
Else
Range("a35").Value = "Why, such a cruel world, you were the Love of my life"
End If

End Sub

message box answer

They got married and had many many happy and beautiful children :-)

This should give you enough to start programming message boxes in VBA by yourself. Good Luck.

 

Please Tweet, Like or Share us if you enjoyed.