Results 1 to 9 of 9
  1. #1
    5 Star Lounger
    Join Date
    Oct 2001
    Location
    San Bernardino, California, USA
    Posts
    734
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Check Boxes formula stuff (2003)

    I have several form related questions:

    In the attached, I want to be able to click on the check boxes and get a score on the right in cell M6. It works but my formula seems really inefficient and I wondered if

    1) there is a way to only allow one check box to be selected (if another is selected, the previous select will unselect). I currently have conditional formatting on M6 to show multiple check boxes.

    2) There is a more efficient formula than all the nested IF's

    3) It is possible to be more elegant so the TRUE & FALSE does not appear.

    Finally, once a check box is inserted, how do you know if it is check box 1, 2, 3 or whatever? I have deleted the text of the text box and also the web alternative and no cannot identify which box is which. I am thinking that if I could identify the boxes, I might be able to use them in my formula instead of the linked cells containing TRUE & FALSE.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Check Boxes formula stuff (2003)

    1) If you want only one to be selected, use option buttons (aka radio buttons) instead of check boxes.
    To create a group of option buttons, place a group box around the option buttons.

    2) and 3) Using (grouped) option buttons, the linked cell will display the index of the selected option button: 1 for the first one, 2 for the second one etc.

  3. #3
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Check Boxes formula stuff (2003)

    Instead of using checkboxes, which are used to check mutually exclusive options, use 5 option (radio) buttons. These do what you want them to be. Use format control to set the cell link and it will indicate (directly) the number of the one that is selected (no need for an if)

    Steve

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Check Boxes formula stuff (2003)

    You only need to set the linked cell of one option button in a group; the rest will be set automatically to the same cell.

    The value of the option buttons is determined by the order in which you created them. So if you want the leftmost one to have value 5, drag the last one you created to the left, etc.

  5. #5
    5 Star Lounger
    Join Date
    Oct 2001
    Location
    San Bernardino, California, USA
    Posts
    734
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Check Boxes formula stuff (2003)

    I understand about the option buttons and the grouping but how do I link the group to a cell? must I link each option button to the same cell then group the option buttons?

    Also, I need to have the index such that the first option button = 5 and the last = 1; how can I do this with the linked cell showing the index of the option button selected?


    --- Also, is it possible to have the option box not visible? I see no way to manage the line color.

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Check Boxes formula stuff (2003)

    No, there is no way to change the line color of the option box. If you want more control, you'd need to use the option buttons from the Control Toolbox, but they are less convenient in this situation, because each option button from this toolbar requires its own linked cell, which will contain TRUE or FALSE, so you'd be back at square one.

  7. #7
    5 Star Lounger
    Join Date
    Oct 2001
    Location
    San Bernardino, California, USA
    Posts
    734
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Check Boxes formula stuff (2003)

    Thank you all for the help. I have completed all but one objective here and that is to protect the sheet from change while allowing the user to check the boxes and enter comments.

    I have unlocked all the option boxes and comment cells but when I protect the sheet, I am unable to use the option boxes. Could you take a look at the attached and tell me what I have done wrong?

    Thank you again.

  8. #8
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Check Boxes formula stuff (2003)

    If you protect the sheet the linked cells must be unlocked since you are allowing the use to change them...

    Steve

  9. #9
    5 Star Lounger
    Join Date
    Oct 2001
    Location
    San Bernardino, California, USA
    Posts
    734
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Check Boxes formula stuff (2003)

    Thank you all for the help - I was able to create what I needed and get the job done on time.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •