• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Using info in a checkbox in a formula

C

Countess

New Member
I have a worksheet where the creator is using the checkbox feature from the Developer toolbar, or so it looks like. Can’t tell if it’s the Form Controls, or ActiveX Controls. Either way, I’m trying to see if there is a way to capture whatever was checked in a cell so I can use the captured content in a formula, either by converting the selected check box to text or linking the check box to a formula or something else.


For example:

Line 1: □ Yellow

Line 1: □ Blue

Line 1: □ Red


If I check the box next to yellow and A1, I want to see if I can have the word yellow appear in A2. From there I will use “yellow” or whatever was checked in formula elsewhere on my document. If there is a similar option which would give me the same result (using Yellow in a formula), please let me know.


We are trying to keep the original using the checkboxes, but if we can’t, I’ll just use the drop-down menu/list feature.


Thank you!
 
Hi ,


To see whether it is a Forms control or an ActiveX control , click on Design mode , and click on the checkbox ; if it is a Forms control checkbox , the checkbox will respond to the click by either checking the checkbox if it was unchecked , or unchecking it if it was checked i.e. the Design mode does not make any difference to the checkbox.


If it is an ActiveX checkbox , the checkbox will be selected when you click on it i.e. it will not check / uncheck the checkbox. The only way an ActiveX checkbox ( or any other ActiveX control , for that matter ) will respond is if you exit Design mode.


Another easier way is to take the cursor to the checkbox ; if it is a Forms control , the cursor will change to a hand , similar to what happens when you hover over a hyperlink. If it is an ActiveX control , there will not be any change.


Another point to note is that checkboxes are not mutually exclusive , so you can have more than one checkbox checked at a time ; if you want that only one option out of the three should be selected , then you should consider using option buttons ( radio buttons ) , which can be grouped together so that they are mutually exclusive.


Assuming that you continue to use checkboxes , the linked cell will allow you to use IF statements to get what you want ; assuming that the three checkboxes are linked to cells P1 , Q1 and R1 , you can use :


=IF(P1,"Yellow",IF(Q1,"Blue",IF(R1,"Red","")))


Narayan
 
Does it matter if it's Forms or Active X in relation to my question? Hope it doesn't especially since I can't access Design Mode, it's grayed out for me, even when I've selected the cell that has the check box in it. Thanks!
 
Hi ,


If Design mode is grayed out , then right clicking on an ActiveX checkbox will not do anything ; right clicking on a Forms control checkbox will display a list of options available for that checkbox.


Since you said that you did not know what kind of a checkbox was present , I thought this information might be of use to you. It doesn't make any difference to resolving your problem.


Narayan
 
Thanks Narayan, I just put that in there in case that had anything to do with answering my questions. Since I don't plan to use it myself, I don't really care. But getting back to may main question, is there a way to link the content in a checked box to a cell or formula? This is in relation to my other post: http://chandoo.org/forums/topic/conditional-formatting-color-whole-row-based-on-criteria-matching-other-cells which you're helping me out on.


So to tie the 2 together, say people can check boxes for yes and no, and then yellow and blue. I then want to take what they checked (Yes Yellow, No Yellow, Yes Blue, etc) and have related rows below hightlight (per the formula you gave). Makes sense?


I'm thinking I should be able to pull info from the checked boxes, just hope it doesn't involve macros or much VBA - not my forte.
 
Hi ,


I am repeating this from my earlier post ; is this what you are looking for ?


Assuming that you continue to use checkboxes , the linked cell will allow you to use IF statements to get what you want ; assuming that the three checkboxes are linked to cells P1 , Q1 and R1 , you can use :


=IF(P1,"Yellow",IF(Q1,"Blue",IF(R1,"Red","")))


Narayan
 
Sorry to make you repeat yourself, I don't know how check boxes work. Just did a quick study and it looks like I have to link the checkbox (using From Controls FYI) to a cell and that cell will read True if checked and False if not checked. So going back to my other post, I think I got it all to fit and work the way I want, though it may be a little lengthy.


For example: In A column I have my choices (for this example 2 choices per criteria/section), in B column I have my checkboxes, in C column is where the checkboxes are linked, in D column I convert the True/False back to the text in A column (1 statement to cover both choices, i.e. =IF(C1,"Yes", IF(C2,"No", "N/A")) or I guess I could write instead: =IF(C1,A1, IF(C2,A2, "N/A"))

This is the formula I’m using:

NOT(ISERROR(FIND($D$1,$B9, NOT(ISERROR(FIND($D$3,$B9, NOT(ISERROR(FIND($D$5,$B9)))))))))

Where B9 starts the rows I want to have formatted, linked to which checkbox is marked.


Not sure if there’s a more succinct/simpler/consolidated way of doing this, if so please let me know, but this’ll work for me.


Again, many thanks for your help and patience. I knew there was a way to do this and wish I had turned here days ago. :)
 
You must log in or register to reply here.
Back
Top