Keeping users informed: VBA message boxes

Helen Bradley explains three different methods for sending messages to your users in your macros.

Amessage box is the tool of choice when you need to pass information to your user. Message boxes are easy to code and they do a good job in most routine circumstances. However, there are times when a custom solution is required, so this month, we’ll look VBA message boxes and what to do when they aren’t what you need.

BUILT-IN MESSAGE BOXES

A simple way to pass information to your user is via a VBA message box. When you need to provide information but not get anything in return, you can use a simple statement like this:

Sub DisplayDate() MsgBox “Today is: “& Format(Date, “dd mmm yyyy”), _ vbInformation + vbOKOnly, “Today’s Date” End Sub

This message box provides today’s date to the user and it stays open until the user clicks the OK button to close it and continue.

This second type of message box records a reply from the user in the form of a value representing the button the user presses to close the message box. You can offer your user choices including ‘ Yes’, ‘No’, ‘Cancel’, ‘OK’, ‘Abort’, ‘Retry’ and ‘Ignore’. You must declare and use a variable in which to store the user’s response so you can check it and proceed accordingly.

This is a typical message box of this type:

Sub RespondToUser() Dim userReply As Integer userReply = MsgBox(“Shall I enter Today’s Date into Cell A1?”, _

vbYesNo, “Optional Date Entry”) If userReply = vbYes Then Range(“a1”).Value = Format(Date, “dd mmm yyyy”) End If End Sub

CUSTOM MESSAGE BOX

When you need to provide frequent information to a user, a message box is cumbersome because it requires the user to click a button to continue. In this situation, you can use a customdesigned form that looks like a message box. If displayed in the modal state, the form stays visible as you work and can be updated with new information without requiring user interaction. It can also display custom button text. To see this at work, create a form with a Label, a textbox and a CommandButton. Set the textbox BackColor to ‘Button Face’ and its SpecialEffect to ‘0-frmSpecialEffectFlat’. Set the CommandButton Cancel property to ‘True’ and its Caption to ‘Stop Counting & Exit’.

Enter this in the form code:

Private Sub CommandButton1_ Click() Unload Me End Sub

Private Sub UserForm_ Initialize() Me.TextBox1.Text = CStr(0) End Sub

Enter this in the code window for Sheet1:

Private Sub Worksheet_ Change(ByVal Target As Range) UserForm1.TextBox1.Value = CStr(UserForm1.TextBox1. Value + 1) End Sub

Enter this in a module:

Sub startTracking() UserForm1.Show (modal) End Sub

When you run the startTracking macro, a message box appears which displays a live ongoing count of the changes made to the current sheet. It could, of course, be programmed to display anything you like.

2 STEP 2 This message box allows for interaction so the user can make a choice about how to proceed.

3 STEP 3 This custom solution is a form that stays visible, is updated as you work, and requires no user input.

1 STEP 1 This simple message box delivers information but receives nothing in return.