VBA MSGBOX - A Complete Guide To Message Box Function + ...

Trang chủ » Visual Basic Msgbox Example » VBA MSGBOX - A Complete Guide To Message Box Function + ...

Có thể bạn quan tâm

Contents hide Syntax How to Customize a Message Box and use Different Buttons 1. vbOKOnly 2. vbOKCancel 3. vbAbortRetryIgnore 4. vbYesNoCancel 5. vbYesNo 6. vbRetryCancel 7. vbCritical 8. vbQuestion 9. vbExclamation 10. vbInformation 11. vbDefaultButton1 12. vbDefaultButton2 13. vbDefaultButton3 14. vbDefaultButton4 15. vbAppliactionModal 16. vbSystemModal 17. vbMsgBoxHelpButton 18. vbMsgBoxSetForeground 19. vbMsgBoxRight 20. vbMsgBoxRtlReading Returning Values Real Life Examples to use VBA Message Box Function in Excel 1. Run Macro with a VBA MsgBox 2. Insert a Table into a Message Box 3. Show Message Box on Opening a File Points to Remember Sample File Conclusion

The first thing which I have learned about VBA was using a message box (“msgbox function”). It’s like a simple popup box on your Excel window to show a specific message. If you think about it, you can use it in two ways.

  • Deliver a message to the user.
  • Get a simple response from the user.

Most of the VBA programmers use it in their macro codes to make them more interactive and if you are not familiar with it, I bet after reading this post you will fall in love with it.

Expert Tip: If you are a VBA newbie then learning about VBA message box can be a good idea.

So today in this post, I’d like to share with you all the details about using a message box in Excel and some of the real life examples.

Let’s get started.

Syntax

=MSGBOX(prompt, [buttons], [title], [helpfile, context])
syntax vba msgbox function
  • prompt – A string expression used for displaying as a message. The maximum length of characters is 1024, which depends on the width of the characters.
  • [buttons] – You can use this argument to specify buttons, icon style, button’s identity and modality of the message box.
  • [title] – You can use this argument to specify a title text for your message box.
  • [helpfile] – This argument will help you to specify a help file for the user. The user can access that help file using the help button. If you specify a help file, this mandatory to specify a context number.
  • [context] – A numeric expression that uses to assign a help topic from the help menu.

Note: In the above list, arguments above which are in square brackets are optional, you can skip to specify them.

How to Customize a Message Box and use Different Buttons

You can easily customize a VBA message box with all the available options. For this, there are multiple constants available to use in the msgbox. Let’s have a look…

Understanding VBA Constants

1. vbOKOnly

This gives you a simple OK button. The user can click on it after reading the message to close it. You can also use further a macro to execute once the OK button is clicked.

Sub OKOnly() MsgBox Prompt:="This is a MsgBox ", _ Buttons:=vbOKOnly, _ Title:="MsgBox" End Sub

2. vbOKCancel

This constant gives you an OK and cancel button. Now the user has two options to press OK to proceed or cancel to exit.

Sub OKCancel() MsgBox Prompt:="Is this OK", _ Buttons:=vbOKCancel, _ Title:="MsgBox" End Sub

3. vbAbortRetryIgnore

This allows you to show three buttons for About, Retry and Ignore. The user can abort the operation, can retry or can ignore it.

Sub OKCancel() MsgBox Prompt:="Is this OK", _ Buttons:=vbOKCancel, _ Title:="MsgBox" End Sub

4. vbYesNoCancel

This constant shows three buttons for Yes, No and Cancel. The user can Yes to accept and proceed, No to reject or cancel to close the message box.

Sub YesNoCancel() MsgBox Prompt:="Now, You have three buttons", _ Buttons:=vbYesNoCancel, _ Title:="MsgBox" End Sub

5. vbYesNo

This constant shows two buttons for Yes and No. The user can click Yes to accept and proceed or No to reject.

Sub YesNo() MsgBox Prompt:="Now, You have two buttons", _ Buttons:=vbYesNo, _ Title:="MsgBox" End Sub

6. vbRetryCancel

Now this shows two buttons, retry and cancel. You can use this to ask the user to retry the operation or to cancel it.

Sub RetryCancel() MsgBox Prompt:="Please Retry", _ Buttons:=vbRetryCancel, _ Title:="MsgBox" End Sub

7. vbCritical

This constant shows an icon in the msg box, stating that the message is critical.

Sub-Critical() MsgBox Prompt:="This is critical", _ Buttons:=vbCritical, _ Title:="MsgBox" End Sub

8. vbQuestion

This constant can use when you asking a question to the user.

Sub Question() MsgBox Prompt:="What to do now?", _ Buttons:=vbQuestion, _ Title:="MsgBox" End Sub

9. vbExclamation

This constant will show an exclamation icon with the message.

Sub Exclamation() MsgBox Prompt:="What to do now?", _ Buttons:=vbExclamation, _ Title:="MsgBox" End Sub

10. vbInformation

It will show an icon stating that the message is information.

Sub Information() MsgBox Prompt:="What to do now?", _ Buttons:=vbInformation, _ Title:="MsgBox" End Sub

11. vbDefaultButton1

Use this constant to specify the first button of your msg box as the default button.

Sub DefaultButton1() MsgBox Prompt:="Button1 is Highlighted?", _ Buttons:=vbYesNoCancel + vbMsgBoxHelpButton + vbDefaultButton1, _ Title:="MsgBox" End Sub

12. vbDefaultButton2

Use this constant to specify the second button of your message box as the default button.

Sub DefaultButton2() MsgBox Prompt:="Button2 is Highlighted?", _ Buttons:=vbYesNoCancel + vbMsgBoxHelpButton + vbDefaultButton2, _ Title:="MsgBox" End Sub

13. vbDefaultButton3

Use this constant to specify the third button of your msgbox as the default button.

Sub DefaultButton3() MsgBox Prompt:="Button3 is Highlighted?", _ Buttons:=vbYesNoCancel + vbMsgBoxHelpButton + vbDefaultButton3, _ Title:="MsgBox" End Sub

14. vbDefaultButton4

Use this constant to specify the fourth button of your msgbox as the default button.

Sub DefaultButton4() MsgBox Prompt:="Button4 is Highlighted?", _ Buttons:=vbYesNoCancel + vbMsgBoxHelpButton + vbDefaultButton4, _ Title:="MsgBox" End Sub

15. vbAppliactionModal

This constant will suspend the application (Excel), User has to respond to the msgbox to use application.

Sub OKOnly() MsgBox Prompt:="This is a MsgBox ", _ Buttons:=vbOKOnly, _ Title:="MsgBox", _ End Sub

16. vbSystemModal

This constant will suspend all the applications in your OS, User has to respond to the msg box first.

Sub ApplicationModal() MsgBox Prompt:="This is the Application Modal", _ Buttons:=vbOK + vbApplicationModal, _ Title:="MsgBox" End Sub

17. vbMsgBoxHelpButton

Use this constant to add a help button to your msg box. You can add a help file & a context number to use the help button.

Sub HelpButton() MsgBox Prompt:="Use Help Button", _ Buttons:=vbOK + vbMsgBoxHelpButton, _ Title:="MsgBox", _ HelpFile:="C:UsersPuneet GogiaDesktopsamplehelp.chm", _ Context:=101 End Sub

Once the user clicks on the help button, a help menu will appear.

18. vbMsgBoxSetForeground

This constant will help you make your message box window a foreground window.

Sub SetForeground() MsgBox Prompt:="This MsgBox is Foreground", _ Buttons:=vbOK + vbMsgBoxSetForeground, _ Title:="MsgBox" End Sub

19. vbMsgBoxRight

The text will be aligned to the right by using this constant.

Sub MsgBoxRight() MsgBox Prompt:="Text Is In Right", _ Buttons:=vbOK + vbMsgBoxRight, _ Title:="MsgBox" End Sub

20. vbMsgBoxRtlReading

By using this constant, the message box will flip to the right. This constant is mainly designed for Hebrew and Arabic systems.

Sub MsgBoxRtlReading() MsgBox Prompt:="This Box is Flipped", _ Buttons:=vbOK + vbMsgBoxRtlReading, _ Title:="MsgBox" End Sub

Returning Values

Whenever a user responds to the message box by clicking any of the buttons, a number is generated. This will help you to identify which button is clicked by the user.

ConstantValueDescription
vbOK1OK
vbCancel2Cancel
vbAbort3Abort
vbRetry4Retry
vbIgnore5Ignore
vbYes6Yes
vbNo7No

Real Life Examples to use VBA Message Box Function in Excel

Here I have listed some real life examples for VBA message box and I’m sure these examples will inspire you to use it.

1. Run Macro with a VBA MsgBox

Now with the help of msgbox function, you can ask a user before running a macro. Let’s look the below macro to understand.

Sub SaveThis() Dim Result As Integer Result = MsgBox("Do you want to save this file?", vbOKCancel) If Result = vbOK Then ActiveWorkbook.Save End Sub
save workbook with vba msgbox

You can ask the user to save the workbook and if the user clicks on the OK button the macro code will save the workbook.

2. Insert a Table into a Message Box

You can use vbTab to enter a tabular data in the message box. In this example, the table is starting from cell A1.

Sub AddToMsgBox() Dim Msg As String Dim r As Integer Dim c As Integer Dim rc As Integer Dim cc As Integer Dim myRows As Range Dim myColumns As Range Msg = “” Set myRows = Range(“A:A”) Set myColumns = Range(“1:1”) rc = Application.CountA(myRows) cc = Application.CountA(myColumns) For r = 1 To rcFor c = 1 To cc Msg = Msg & Cells(r, c).Text If c <= cc Then Msg = Msg & vbTab Next cMsg = Msg & vbCrLf Next r MsgBox Msg End Sub

3. Show Message Box on Opening a File

If you look at the below macro, I have used auto_open to create a message to show on the opening of the workbook.

Sub auto_open() MsgBox "Welcome To ExcelChamps & Thanks for downloading this file" _ + vbNewLine + vbNewLine + "You will discover a detailed explanation about MsgBox Function here." _ + vbNewLine + vbNewLine + "And, Don't forget to check other cool stuff." End Sub

Points to Remember

  1. You can’t add four buttons in a message box other than you are using the help button as a fourth button.
  2. For creating a help file for your message box, you can refer to Ken’s post.

Sample File

Download this sample file from here to learn more about this.

Conclusion

Message box not only helps you to deliver a message but also make a creative impression on the user. You can give them a message and get a simple response.

And as I said, if you’re new to VBA then learning about using a message box is one of the best things. I hope this post helped you get closer to your VBA mastery dream but now tell me one thing.

Do you think MsgBox function is one of the coolest things in VBA?

Make sure to share your views with me in the comment section, I’d love to hear from you and don’t forget to share this tip with your friends.

More on VBA

  • Add a New Sheet with Name using VBA
  • How to Record a Macro in Excel
  • VBA Option Explicit

Từ khóa » Visual Basic Msgbox Example