Message Box in Excel VBA

 

In this article, we will learn the MsgBox function in Excel VBA, Following are the buttons argument settings of msgbox vba:

Constant Value Description
vbOKOnly 0 Display OK button only.
vbOKCancel 1 Display OK and Cancel buttons.
vbAbortRetryIgnore 2 Display Abort, Retry, and Ignore buttons.
vbYesNoCancel 3 Display Yes, No, and Cancel buttons.
vbYesNo 4 Display Yes and No buttons.
vbRetryCancel 5 Display Retry and Cancel buttons.
vbCritical 16 Display Critical Message icon.
vbQuestion 32 Display Warning Query icon.
vbExclamation 48 Display Warning Message icon.
vbInformation 64 Display Information Message icon.
vbDefaultButton1 0 First button is default.
vbDefaultButton2 256 Second button is default.
vbDefaultButton3 512 Third button is default.
vbDefaultButton4 768 Fourth button is default.
vbApplicationModal 0 Application modal; the user must respond to the message box before continuing work in the current application.
vbSystemModal 4096 System modal; all applications are suspended until the user responds to the message box.
vbMsgBoxHelpButton 16384 Adds Help button to the message box
VbMsgBoxSetForeground 65536 Specifies the message box window as the foreground window
vbMsgBoxRight 524288 Text is right aligned
vbMsgBoxRtlReading 1048576 Specifies text should appear as right-to-left reading on Hebrew and Arabic systems

 

Let us see how vba message box can help the user in deleting all the contents of the cells in the current worksheet.

Following is the snapshot of data:

image 1

 

 

We need to follow the below steps:

  • Click on Developer tab
  • From Code group, select Visual Basic

image 2

 

  • Let us insert a Command Button

image 3

 

  • Enter the following code in the current worksheet module:-

Option Explicit

Private Sub CommandButton1_Click()

Dim answer As Integer

answer = MsgBox(“Do you want to delete all the cells in the current sheet?”, vbYesNo + vbQuestion, “Delete all cells”)

If answer = vbYes Then

Cells.ClearContents

Else

    ‘do nothing

End If

End Sub

 

image 4

 

  • As we click on Command button, the following prompt will appear

image 5

 

  • Click on Yes button to delete all the cells.
  • Result will be as follows

image 6

 

In this way, we can delete all the cells using command button in Excel VBA.

xlsx-1567

Download-Message Box in Excel VBA

 



2 thoughts on “Message Box in Excel VBA

  1. How I can stop the “X” button from the upper right corner to close the msgbox, practically force the user to respond with assigned buttons. Something similar with UserForm_QueryClose(Cancel As Integer, CloseMode As Integer), cancel = false, and post a message.

    • Hi Csaba,

      “X” button works just like the CANCEL button. It returns the vbCancel value. So, if you have CANCEL button in the message box then clicking on “X” button will give the same result as clicking on the CANCEL button.

      If the message box contains only the OK button, then clicking on the “X” button will perform the similar task as clicking on the OK button

Leave a Reply

Your email address will not be published. Required fields are marked *

*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>