Viewing 11 posts - 1 through 11 (of 11 total)
  • Excel survey: pass/fail
  • mrsheen
    Free Member

    I’m creating a quiz in Excel and I’d like to have users click on a button and get a message telling them if they’ve passed or failed.

    I’ve created a list of questions and used Option Button (ActiveX Control) for the answer options. I’ve used Group Boxes (Form Control) and linked the buttons to a cell next to each question so it shows a number between 1 and 3 depending on which of the three answers is selected.

    How might one do this?

    Thanks

    tinribz
    Free Member

    I would start by ditching excel and using ms forms or one of the free online survey tools.

    Killer
    Free Member

    wihtin the macro functionality of Visual Basic you’ll need to create some level of command instruction to produce a box.

    msgbox is the command you’re probably looking for to create that. A quick google will help on the syntax for it.

    but where you do the ‘marking’ of the scores could be in excel or in the program.
    i’d suggest the program with a series of nested If’s boxa = A then, if boxb = B then etc pass= true, else pass=false end if

    heop that made sense, but I agree with above that excel may be a clunky way of doing what you’re after

    Fresh Goods Friday 696: The Middling Edition

    Fresh Goods Friday 696: The Middlin...
    Latest Singletrack Videos
    mrsheen
    Free Member

    Thanks. Forms has been muted by colleagues but some users sadly might not have access or some similar issue.

    I’ll look into your suggestions so thanks again.

    leffeboy
    Full Member

    Rather than press a button why not just have a cell at the end that shows pass or fail, that would be easier.  The problem with a button is that then you need to run code and you get into the whole problem of macros being enabled.  They might be disabled because the file was downloaded from the internet or it was sent by email.  Just have a cell at the end that shows PASS/FAIL and you are done and it is much easier

    Your version using ActiveX controls and group  boxes sounds very snazzy but if you end up distributing it to lots of people who can’t run it then you are stuffed.  I would keep it as simple as you possibly can so it works.  Minimum you need to send it to a few people to see what happens when they open it

    or use MS Forms/Google forms

    dangeourbrain
    Free Member

    Can’t they just click “reply [to mrsheen who’s job it is to do silly survey stuff for marking]” in outlook. That’d be best.

    sockpuppet
    Full Member

    Thanks. Forms has been muted by colleagues but some users sadly might not have access or some similar issue.

    Mooted. MOOTED.

    I know it doesn’t help here, but it’s a different word.

    It means a different thing. Please use it if that’s what you mean.

    Sorry.

    thisisnotaspoon
    Free Member

    wouldn’t survey monkey or similar do that, assuming it’s not blocked then anyone you e-mail can access it. Whereas macros etc assume the person at the other end clicks yes to a series of doom laden messages from Microsoft about mallware/viruses.

    Simplest way would be to just hide a few columns that say =if(B5=B, 1,0), where B5 is the cell they answer in, and B is the correct answer.

    Then, =countif(range, etc …. to determine if everything has been answered, then sum the solutions to the formula in the last statement to get a score. if(score>pass mark, “PASS”, “FAIL”)

    footflaps
    Full Member

    Whereas macros etc assume the person at the other end clicks yes to a series of doom laden messages from Microsoft about mallware/viruses.

    There are ways around that, but they throw up their own issues. E.g. I package my Excel tools into a .exe which launches Excel first, enables macros etc and then runs the underlying VBA tool. However, you’re now emailing round .exe files rather than .xlsm files; so can easily be blocked by email filters etc….

    fazzini
    Full Member

    MS Forms allows you to share the form/quiz/survey via an independent web link. As long as the participants have access to any web accessing device you can get them to complete it. You can also create QR codes so any QR enabled camera on a phone or tablet can access this. We use Forms all the time now as so much simpler and easier than Surveymonkey etc.

    andrewreay
    Full Member

    If it must be excel, just use conditional formatting to ‘reveal’ “Pass” or “Fail” when the last question is answered.

    No need for macros and active stuff.

    Keep it simple.

Viewing 11 posts - 1 through 11 (of 11 total)

The topic ‘Excel survey: pass/fail’ is closed to new replies.