Collect Data From Users in Excel VBA
You can use Excel VBA to show messages to someone who’s using your workbook. This messages 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?)
But sometimes you want to get information from a user, instead of giving it. Let’s look at a couple of ways to do that, in Excel VBA.
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.
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