A VBA MsgBox Replacement
July 17, 2009 • JP • No Comments • Rate This Article
• Links to this article
In VBA Tips, Tricks and Best Practices, Part Three of Four I mentioned encapsulating the MsgBox function to handle user input. Here is the function I wrote to handle that.
It uses an enumerated section to duplicate the button arguments used in the original MsgBox function. This should be placed at the top of a standard module. (Hint: You can get this list by typing 'MsgBox' into the VB Editor or the Immediate Window and pressing F1.)
vbOKOnly = 0
vbOKCancel = 1
vbAbortRetryIgnore = 2
vbYesNoCancel = 3
vbYesNo = 4
vbRetryCancel = 5
vbCritical = 16
vbQuestion = 32
vbExclamation = 48
vbInformation = 64
vbDefaultButton1 = 0
vbDefaultButton2 = 256
vbDefaultButton3 = 512
vbDefaultButton4 = 768
vbApplicationModal = 0
vbSystemModal = 4096
vbMsgBoxHelpButton = 16384
vbMsgBoxSetForeground = 65536
vbMsgBoxRight = 524288
vbMsgBoxRtlReading = 1048576
End Enum
Here is the new function, called MessageBox (a la VB.NET). There are two optional arguments:
- The buttons argument duplicates the function of the same argument in the original MsgBox function.
- title is declared As Variant so we can use IsMissing to check if it was passed.
Note that I left out the last two optional arguments of MsgBox; I never use them, so to avoid complication I didn't include them.
Optional title As Variant) As VbMsgBoxResult ' As Integer
' rewrite of VBA's MsgBox Function
' will return one of the following seven values:
' vbOK - 1
' vbCancel - 2
' vbAbort - 3
' vbRetry - 4
' vbIgnore - 5
' vbYes - 6
' vbNo - 7
Dim titleString As String
If IsMissing(title) Then
titleString = Application.Name
Else
titleString = CStr(title)
End If
MessageBox = MsgBox(prompt, buttons, title)
End Function
Sample usage:
Dim result As Integer
result = MessageBox("hello", vbInformation + vbYesNo, "My title")
Select Case result
Case 1, 4, 5, 6
MessageBox ("you want to continue!")
Case 2, 3, 7
MessageBox ("you want to stop")
End Select
End Sub
You'll notice that the new function works just as well as the original, maybe better (since it has less options to worry about), and you can use it with or without returning a value.
In the example above, we capture the result of MessageBox in the variable result, then use a Select Case statement to determine which option was selected. The MessageBox function is then used without capturing the return value, to simply display a message box to the end user.
VBA ·
Rate This Article:
(No Ratings Yet)
Loading ...What now?
Subscribe To The Blog
Email This Article
Follow Me On Twitter
My Facebook Page
Or just keep reading:
I'm just an average guy who writes VBA code for a living. This is my personal blog. Excel and Outlook are my thing, with a sprinkle of Access and Word here and there. Follow this space if you want to learn more about VBA. Keep Reading »
Previous Post: VBA Tips, Tricks and Best Practices, Part Four of Four
Next Post: Contest Announcement
Speak Your Mind
Tell us what you're thinking...Certain comments (including first-time comments) are subject to moderation and will not appear immediately. Please view the Comment Policy for more information. To post VBA code in your comment, use tags like this: [cc lang='vb']Code goes here[/cc].