Analyst Cave

Mastering the VBA MsgBox

Today shortly on how to master the Excel MsgBox. Although, the MessageBox is a common control used in most Window applications many VBA developers underestimate its usage – often making custom UserForms for tasks where the Excel MsgBox could just as well do the trick. An example? User Interaction – when user input is needed many VBA devs create custom UserForms not knowing that the MsgBox can return a Yes/No, Retry/Cancel, Ok/Cancel response when configured properly.

Example Excel MsgBox question

This example will produce a simple Excel MsgBox question with Yes/No buttons:

If MsgBox("Do you agree?", vbYesNo, "Question") = vbYes Then
    'If Yes...put some code here
Else
    'If No...do this
End If


Easy right? Yet this is just a sample of what you can do with the MsgBox. See the link above to learn more on how to efficiently utilize the Excel MsgBox in your VBA projects!

Common uses for the Excel MsgBox

The Excel MsgBox can be used in multiple different ways. See some valuable mentions below:

MsgBox: Messages, Notifications, Questions

The common VBA message box can be well used to interact with users to show messages (information, errors, notifications etc.) or just as well ask simple questions (Ok/Cancel, Retry/Cancel). Let us start by analyzing the parameters of the MsgBox function.

Function: MsgBox
Parameters:

Now some examples:

Retry with Critical Error Icon

Call MsgBox("Try to run the VBA code again?", vbRetryCancel + vbCritical, "Error")

Align message and Question Icon

Call MsgBox("Align this to the right hand side", vbQuestion + vbMsgBoxRight, "Some Message")

User interaction with the MsgBox
The message box is a great tool for asking simple Yes/No questions within your VBA code. See some examples below:

If MsgBox("Do you agree?", vbYesNo, "Question") = vbYes Then
    'If Yes...put some code here
Else
    'If No...then do this
End If

This will show the following question and pause the code until the user clicks on one of the buttons:

Similarly you can create a retry question which is convenient for error handling:

  On Error Goto ErrorHandl:
Retry:
  'Some code here..
ErrorHandl:
  If MsgBox("Retry?", vbYesNo, "Question") = vbRetry Then
     Goto Retry  
  End If

Let me know what you think!

Exit mobile version