Best approach to determining each option selected from multiple option button groups?

Hello all,

I am trying to create a questionnaire using a vba userform - there are about 17 questions, each with multiple options (Yes/No/Unsure for part 1 questions, part 2 questions have Low/Med/High/Unsure) - please see attached...

How do I best determine the value of the response for each question?

I have already grouped each set of Y/N/U with a unique Group name - how do I output to answer sheet what is the selected option for Q1?

Because there are 17 questions, I am trying to write some efficient code, rather than one really long if statement.

Any suggestions?

Thanks,

Ian

Example 1.PNG

Hi, Ian!

When you ask about determining the value of the response for each question, do you mean how do you take the answer the user chose and put it in your worksheet? Or do you mean how to you decide what numerical value to assign a response in order to calculate an overall score for the questionnaire, such as for social science research? (Even though that's not an Excel question per se, I may be able to help you with that, but I'd need to know more about your questionnaire.)

Susie

Hi Susie,

I should have been a bit more clear:

Yes, for each user that responds I want to be able to capture their answers into a worksheet like this:

User #1	Ans for Q1	Ans for Q2	etc...	Conclusion for User #2
User #2	Ans for Q1	Ans for Q2	etc...	Conclusion for User #2

So... in regards to the 'Conclusion' and your question about calculating a score for it - you are almost spot on! The questionnaire is to determine which one of the 4 categories the user would fall under and that would be my next task, so I may as well clarify it now:

So, for example For Question 1 - it is predetermined that the user can select an answer of Yes/No/Unsure and with the selection comes a score for each of the categories like so:

Response	Category 1	Category 2	Category 3	Category 4
Yes	1	1	0	0
No	0	0	1	1
Unsure	0	0	0	0

i.e. for Question 1, if the user responds Yes - then they have a TRUE or 1 point under Category 1 and Category 2 - this will be the same for all the other questions, but different responses score differently under each category for other questions. At the end I will need to tally up all the score and the highest scoring category determines the overall user category.

I need to record the individual answers to each question and the overall category.

So, at the moment I have a group of option buttons assigned to each question - they don't do anything at the moment though, other than be clickable and mutually exclusive within that group of buttons - how do I determine which option button was selected within that group, so that I can record the selected answer and then also tally up the scores?

How do I best implement this relatively efficiently in vba userform? I know I could go down the route of long nested if-then statements, but i'm just wondering if there's an easier way of going about it?

Thanks for your help!

Ian