If/Then Checked Boxes

[Schmidiot]

Hello,

I am trying to set up my spreadsheet so if you check a box it will change if you can or can not click on other boxes on the sheet. For example, if you go on Amazon and search for computers, you can change what becomes available based on if you check a specific brand or screen size it will then make the next set of boxes you can check available only if the options from your previous choice match the next ones. I am trying to use an if/then set-up in order to say something like: if the box in C11 is checked, don't make the boxes in C13, C14, and C15 able to be checked.

Another way I was thinking of doing it would be to have the boxes turn either red or green after another one has been checked based on if I want them to be available to be chosen.

Thanks!

[davesexcel]

What have you got so far?

[Schmidiot]

I tried using this to change the color of one cell based on if a box was/was not checked but I haven't had much luck:

Please Login or Register  to view this content.

I am currently trying to use conditional formatting in order to just have the color change based on if one box is or isn't selected. Ideally i'd like to have the boxes set up so they are unable to be selected once a previous box has been chosen, but as of right now i'm finding more information on getting the color to change so that is what I have been starting with.

Thanks.

[AlphaFrog]

You are using Form-type check boxes. You would not put the code in the worksheet change procedure.

Put thhis code in a standard code module e.g. Module1 and assign the macro to Check Box 11

Please Login or Register  to view this content.

[Schmidiot]

Thank you. This code runs but when I check the box I receive an error with this line:

Set cb = ActiveSheet.CheckBoxes("Check Box " & i)

If I just wanted to run a simple version to test it out, what would you recommend to do for having Check Box 1 (B1) and Check Box 2 (B2) and by checking Check Box 1, cell A2 would turn red?

So far I have gotten this to work by doing conditional formatting and using:
=AND($A1="Test2",$D$1)

This is fine for just one word answers, but what I am looking to do will involve combinations where some options have more than one answer. For example, if I check box 1 my options are only a, b, and d. Box 2 will have a, b, c, and d, but box 3 only has option e. I want to be able to sort through this so only box 3 turns red because box 2 still has options that overlap with box 1.

[AlphaFrog]

Thank you. This code runs but when I check the box I receive an error with this line:

Set cb = ActiveSheet.CheckBoxes("Check Box " & i)

The suggested code effects checkboxes by Name. It was designed to work with Check Box 11 and then change Check Boxes 13, 14, and 15

If I just wanted to run a simple version to test it out, what would you recommend to do for having Check Box 1 (B1) and Check Box 2 (B2) and by checking Check Box 1, cell A2 would turn red?

Please Login or Register  to view this content.

[Schmidiot]

The suggested code effects checkboxes by Name. It was designed to work with Check Box 11 and then change Check Boxes 13, 14, and 15

Please Login or Register  to view this content.

Thank you! This worked perfectly!

Edit: I am now trying to alter the code a bit so if I select the box it changes the color of multiple check boxes instead of just the 1 by adding in a line for the next check box, but I am unable to get more than one to change at a time. I am trying to do this by adding more ElseIf conditions and listing my changes for the other boxes I want to change.

Please Login or Register  to view this content.

I think my issue with this code is that i'm doing an ElseIf and I need it to to be an "and". So when the check box is selected it will change box 178 and 179. The code above is running, but it is only changing the color of box 178.

I have also tried this (But I get a type mismatch error when I try this):

Please Login or Register  to view this content.

[AlphaFrog]

Please Login or Register  to view this content.

[Schmidiot]

Please Login or Register  to view this content.

This is exactly what I needed, thank you for all of your help!

[Schmidiot]

One last quick question, would it be very difficult to alter the code so instead of just the check box changing color the whole cell (or the row that the cell is in) would change color?

[AlphaFrog]

One last quick question, would it be very difficult to alter the code so instead of just the check box changing color the whole cell (or the row that the cell is in) would change color?

Please Login or Register  to view this content.

[Schmidiot]

Please Login or Register  to view this content.

Perfect, thank you so much!

[Schmidiot]

Does anyone know if there is a way to get the cell color to change back to the color that it originally was rather than back to white (ie. some are green, some are yellow, some are orange, and when I deselect the check box they all turn white but I want to get them to go back to their original fill)?

[nigelog]

deleted - didnt see original colour as being other than "no Fill"

you would need to record what was what before your change as a point of reference

[Schmidiot]

deleted - didnt see original colour as being other than "no Fill"

you would need to record what was what before your change as a point of reference

Thank you!