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?)

CustMsg06

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.

VBEBeforePrint05

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.

ExcelVBAInput01

Now, if you try to print with no customer name, the message box shows the Yes and No buttons, with the new text.

ExcelVBAInput02

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.

ExcelVBAInput03

So, if someone clicks the Yes button, it returns a numeric value of 6. We’ll change the code to capture this.

  1. We’ll add a variable, lRsp, to the code, to store the returned value.
  2. At the start of the MsgBox line, add the lRsp variable, and enclose the MsgBox arguments in brackets.
  3. Add an If…End If statement, to cancel the printing if the returned value is not equal to 6.

ExcelVBAInput04

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

_______________

You may also like...

11 Responses

  1. Patrick Matthews says:

    “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 :)

  2. AlexJ says:

    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.

  3. AlexJ says:

    @ 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.

  4. Patrick Matthews says:

    AlexJ, I feel your pain. These usually cheer me up.

  5. @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.

  6. Patrick Matthews says:

    Glad to help, Debra, although it looks like I bollixed up the tag a little bit :)

  7. Patrick Matthews says:

    @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

  8. AlexJ says:

    @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)

  9. @Patrick At least the link was correct! The tag is fixed now (I think)

    @AlexJ, ah, thanks — now I see what you’re doing.

  10. Dan says:

    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?

  11. 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.

Leave a Reply

Your email address will not be published. Required fields are marked *