You can use Excel VBA to show messages to someone who’s using your workbook. A message can be simple, with just an OK button, or give people options, with Yes and No buttons.
That lets you collect data from users, and then complete macro steps based on which button they clicked.
Simple Message with OK Button
This simple message lets the user know that a customer name must be selected, and there’s an OK button to click after reading the message. (No one would ever click the OK button without reading the message, would they?)
Message With Yes and No Buttons
But sometimes you want to get information from a user, instead of giving information. This video shows how to create a message with Yes and No buttons, and then run macro steps based on which button is clicked.
There are written steps below the video.
Add Buttons to an Excel Message Box
The message box that’s shown above appears if someone tries to print an order form, and a customer name hasn’t been selected. Here’s the code that we created in the previous blog post on using Excel VBA to show messages.
A message box can return information to Excel VBA, as well as give information to the user. Instead of a single OK button on the message box, you could show Yes and No buttons. Perhaps the user needs to print an order form occasionally, without a customer name.
Change Message Text
We’ll change the message text, to ask, “Print without Customer Name?” and we’ll change the message box style from vbCritical to vbYesNo.
Now, if you try to print with no customer name, the message box shows the Yes and No buttons, with the new text.
Change the Button Behaviour
With the new message box code, we have two buttons, but it doesn’t matter which one you click – the printing is still cancelled.
We’d like to change the code so that if you click No, the printing is cancelled, and if you click Yes, the order form is printed.
When someone clicks a button in a message box, it returns a numeric value. There’s a list in the Excel VBA Help that shows the value returned by each button.
So, if someone clicks the Yes button, it returns a numeric value of 6. We’ll change the code to capture this.
- We’ll add a variable, lRsp, to the code, to store the returned value.
- At the start of the MsgBox line, add the lRsp variable, and enclose the MsgBox arguments in brackets.
- Add an If…End If statement, to cancel the printing if the returned value is not equal to 6.
Download the Sample File
To see the order form and the completed Excel VBA code, you can download the Excel order form. Enable macros when the file opens.
Use an Excel UserForm
If you need to collect more than a Yes or No response from your users, you can use an Excel UserForm.
There are written instructions and video tutorials here: Excel UserForm With ComboBoxes
_______________
“No one would ever click the OK button without reading the message, would they?”
LOL!
Alas, I am finding more and more that too many users just hot Enter to clear a MsgBox without really reading it. Sometimes, I rely instead on UserForms that do not have a default CommandButton. That way hitting Enter doesn’t clear the form, and a mouse click is required.
Still does not actually force people to read and understand messages. Our apps could be perfect if we could just eliminate the end users 🙂
Debra,
I found recently that using the enumeration for the message box result is helpful.
I have written “If lRsp 6? as “If lRsp vbYes” (since the value of vbYes is 6) for a long time. But by dimming lRsp as “vbMsgBoxResult” (as shown in the QuickInfo line in your example)instead of “Long”, the dropdown appears as you write the line, making it easier to code and debug.
Also, dimming a variable as “vbMsbBoxStyle” makes it easy to vary the presentation of the message box in code without having to remember those tricky integer constant values.
@ Patrick
My favourite is when I get a call from a user who informs me that the excel app has failed. He can’t remember what the error message said. Could I come and fix it?
When I get there, the message says “Call App Administrator”. He couldn’t even read that.
Sometimes, I consider adding a message like
“Excel User is a Dummy. Press OK to Confirm”.
As far as eliminating the end user, unfortunately the only way to have an unblemished customer service record is to have no customers, not a good option.
AlexJ, I feel your pain. These usually cheer me up.
@Patrick @AlexJ, maybe we can use CAPTCHA technology in our message boxes, to force those pesky end users to slow down and read what’s on the screen. Make them type the 3rd word in the message, before the OK button works.
@AlexJ, thanks for the suggestion re enumeration. I see how the vbMsgBoxResult variable helps, when writing the lRsp line. I get the vbMsgBoxStyle drop down list when adding the MsgBox arguments though, even without dimming a variable. Maybe I’m missing something?
@Patrick, thanks, those posters are very inspiring, especially on a snowy Friday.
Glad to help, Debra, although it looks like I bollixed up the tag a little bit 🙂
@Debra, I think AlexJ was referring to getting IntelliSense to show you the enumeration values when you work with the variables, such as:
If MyVar = vbYes Then
‘code
End If
@Debra & @Patrick
What I meant wrt vbMsgBoxStyle was for a variable message scheme like:
dim Btn as vbMsgBoxStyle
dim Rtn as vbMsgBoxResult
If Bool1 then
msg = “good message”
btn = vbOKOnly + vbInformation
else
msg = “bad message”
btn = vbOKOnly + vbCritical + vbDefaultButton2
end if
Rtn = msgbox(msg,btn)
@Patrick At least the link was correct! The tag is fixed now (I think)
@AlexJ, ah, thanks — now I see what you’re doing.
You can also choose which button acts as a default, to stop users just hitting Enter and proceeding with a Yes/OK response, which No/Cancel would be more common (i.e. “Do you *really* want to do that?”).
AlexJ provides an example above with …”+ vbDefaultButton2?
Thanks Dan, that’s a good idea, and AlexJ’s code changes the icon on the button too, which might help get the user’s attention.