08-13-2020 10:24 AM
08-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?
08-13-2020 10:30 AM
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.
08-13-2020 11:08 AM
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
08-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):
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).
08-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.