I'm trying to create a base to use for some simple calculations in excel. I'm not a professional user so my coding skills are very limited. Here is a couple of questions:

1) I have some tables, where it would be cool to have a feature to check boxes for values I use in calculations. However, I have problems using check boxes and option buttons in formulas. Is there any clever way to do this? For an example, I use check boxes and option buttons to pick a value and use that value later on in formula to summarize it with other values.

2) How do I group option buttons to separate questions? I have several questions which require option buttons, but now it acts like I had only one choice between all of my questions.



1) Most controls like checkboxes and radio buttons can be linked to a cell. To do this, right-click on the control and go to Format Control; you'll find an option here for Cell Link. Set this to a cell of your choice, and the control will store its value in that cell.

Different controls will have different values to store, of course. A checkbox control will simply store True or False; radio buttons inside a frame will store a number, indicating which button has been selected.

You can then use this cell value in formulae.

2) Use a Group Box Control to group your radio buttons together, allowing controls inside one group to return their values to a single cell while controls in a different group return to a different cell.

In this sample: Control Groups

...I created the two groups, Group Box 6 and Group Box 10, first then added the option buttons after. I then right-clicked on Option Button 7 and set its cell link to B2; all the other option buttons in Group Box 6 automatically picked up the same setting. I then repeated the process with Option Button 11, and set its cell link to B10. Now when I click one of the option buttons, it puts the appropriate number into those cells.

I've used Form Controls here rather than ActiveX controls; for your purposes, I would recommend sticking with Form Controls. ActiveX controls are a little more powerful, but they work better with VBA than with formulae. For a relatively simple setup, stick with Form Controls.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.