Wrapping the call to the VBA MsgBox

The MsgBox() function in Access VBGA lets you display a standard Windows dialog to get a Yes/No answer from the user. The dialog is reliable and familiar but the VBA call to this function can be a bit verbose:

If MsgBox ("Are you sure", _
  vbQuestion + vbYesNo, _
  "Delete record") = vbYes Then...

The MsgBox function then gives you a return value of 6 or 7 so you need to write further code to determine whether the user said 'Yes' or 'No'. The task is not difficult but it does become tedious and the code looks cluttered.

You can make life a lot easier by writing a function to wrap the MsgBox call. We use a function named "Confirm" which takes the text and title as parameters and converts the numeric return from MsgBox into True or False.

The body of the function is very simple:

Public Function Confirm (strMessage As String, strTitle As String) As Boolean
Dim bytChoice As Byte

bytChoice = Msgbox(strMessage, _
  vbQuestion + vbYesNo, _

If bytChoice = vbYes Then
  Confirm = True
  Confirm = False
End If

End Function

The VBA code to use this function is much easier to write than the call to MsgBox because you can embed it into an If statement:

If Confirm("Are you sure", "Delete record") Then...

An additional bonus in our work is that we have no longer have to remember the different calling conventions and the different names for the constants in the FoxPro and Access messageboxes. Both languages use the standard Windows dialog and both require a parameter of 36 and will return 6 or 7 as the result. The difference is that FoxPro calls it with the MessageBox() function rather than MsgBox() and has constants named IDYES and MB_ICONQUESTION corresponding to vbYes and vbQuestion in Access Basic. We've now got a "Confirm()" function written in both languages so we don't have to worry about these annoying differences any more.

More tips from Alvechurch Data

More tips from Alvechurch Data

Example of an Access class module

Using a VBA class module to handle messagebox dialogs

Read More

Messagebox and alternatives

The Windows messagebox is only one way of getting information to the user.

Read More