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.
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:
Public Const sAppName = "MyApp"
Public Function pMsgbox(sPrompt As String, _
Optional nButtons As Long = 0, _
Optional bEnd As Boolean = False, _
Optional bErrClear As Boolean = True) As Variant
' not clearing errors will also display error details
If bErrClear Then Err.Clear
If Err.Number Then sPrompt = sPrompt & "||Error #: " & CStr(Err.Number) & "|Source: " & Err.Source & "|Description: " & Err.Description
' pipes for new lines, more thans for tabs and clean out CRs
sPrompt = Replace(Replace(Replace(sPrompt, "|", vbLf), ">", vbTab), vbCr, vbNullString)
While Left(sPrompt, 1) = vbLf
sPrompt = Mid(sPrompt, 2)
While Right(sPrompt, 1) = vbLf
sPrompt = Left(sPrompt, Len(sPrompt) - 1)
' show what is happening behind the dialog
Dim bASU As Boolean
bASU = Application.ScreenUpdating
Application.ScreenUpdating = True
pMsgbox = MsgBox(sPrompt, nButtons, sAppName)
If bEnd Then pStopper
Application.ScreenUpdating = bASU
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.