Excel-lent Message Box

The message box is for communicating with the user and possibly getting a response of some kind that does not envolve typing as InputBox. It can present the user with a short success report at the end of a macro. Also on many occasions it is used to convay bad news of something not going exactly as planned. Specifically in the latter scenario you typically have to write several lines of code on how to branch out of the main flow. This lead me to write this enhanced version of a message box.

pMsgBox

This version requires for a declaration of a (public) constant sAppName value that is used as the message box title. Also the function uses the pStopper procedure if termination is designated. Here is the code with the required declaration:

The function accepts the same two first parameters as the standard MsgBox and returns the same values.

Parameter sPrompt is the only one required. It is the text to be displayed. However this text can contain formatting characters; you can use pipe “|” to create a new line and greater than “>” to intend with a tab character.

You might want to declare a string variable that collects a report from along the macro process and then display it at the end of the process.

Parameter nButtons accepts the same combinations as the standard MsgBox (such as vbQuestion+vbYesNo).

Parameter bEnd can be set to True (default is False) in order for the procedure to terminate the macro using pStopper.

Parameter bErrClear can be set to False to display the current error number, source and description as a formatted addition to the sPrompt string. By default error status is cleared and the calling macro does not get the error status back from pMsgBox.

The last two parameters in unison can be used for intelligent error handling of calling procedures.

Updated on February 12, 2017 at 10:42 am EET

Leave a Reply