Aug 13 2020 10:24 AM
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?
Aug 13 2020 10:30 AM
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
Aug 13 2020 10:34 AM
@Andrew_RW I will try setting the boxes again but here's the file anyway.
Aug 13 2020 10:36 AM
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.
Aug 13 2020 10:47 AM
There is only 1 option button in the file, what did you say was the issue in the initial message?
Aug 13 2020 11:08 AM
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?
Aug 13 2020 11:19 AM
@wumolad Here is the file with more questions and options.
Aug 13 2020 11:41 AM
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).
Aug 13 2020 01:40 PM
Thanks @mathetes
I'll give that a try. I appreciate your help.
Aug 13 2020 10:30 PM
@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.