Using form controls

[Andrew_RW]

I'm having difficulties using a control form box. I am trying to give 4 options for each of 20 questions with each answer giving a different score in each question. I can't seem to make the control boxes only work for each question. I've tried grouping the control boxes for each question but nothing stops all the control boxes linking to one cell on the spreadsheet. 

Can someone advise me please? 

[#2]

Hi @Andrew_RW 

Did you create a copy for the control boxes or you created each box for each question?

It's possible they are linked together if they were created by copying from another box. 

You can share the file after removing confidential information, so we can have a look.

Cheers

[#3]

@Andrew_RW I will try setting the boxes again but here's the file anyway. 

[#4]

Thanks @wumolad 

I've just replied to my self! New to this! I thought I had set them up differently but maybe not.

Here's the file.

[#5]

@Andrew_RW 

There is only 1 option button in the file, what did you say was the issue in the initial message?

[#6]

Hi @wumolad 

I have 20 questions for which I have 4 options for each question. I am trying to get one set of 4 options for each question but every time I try to start a new set of control boxes for a new question it links with the previous 4 control boxes. When I try to start a new option box it says 'Option Box 33' etc

Any ideas?

[#7]

@wumolad Here is the file with more questions and options.

[#8]

@Andrew_RW 

How about an option that doesn't require VBA. Just uses Data Validation for the drop down, and then a simple formula to put the 1 in the appropriate box. Here's the formula I put in cell D3 in the attached (I deleted the macro-dependent rows since this was just a demo):

=IF($B3=D$2,1,0)

That formula, copied to all the other relevant cells, compares the answer selected with the column heading.

I will admit to being biased against macros. There are times when they may be essential, but it's often possible to create a simpler and equally effective solution with Excel's pre-existing functions (and a smaller file to boot).

[#9]

Thanks @mathetes 

I'll give that a try. I appreciate your help.

[#10]

@Andrew_RW I believe the problem you are facing is due to the fact that you have grouped the button objects rather than enclose the option buttons that belong together inside a group box.

 

In the attached example, I have created two group boxes (also from the Developer ribbon. Buttons 2 and 3 are linked to one cell (C4) and buttons 5 and 6 to another (C14). Buttons 7 to 10 are all linked to H4, but I have grouped buttons 7 and 8 (and then buttons 9 and 10) by selecting them and than right-click and select "Group". This groups the buttons as objects so that you can select, resize, move or format then all at once, but it does not have the same effect as grouping them inside a group box.