Automatic setting of check boxes based on other check boxes?

**ArchiveMike** · 07-30-2012, 02:19 PM

I am building a spreadsheet to price software options and I need to include the option to select all options. When one of the individual options are selected, I want the all options check box to be false. Likewise, if someone selects the all options checkbox, I want the others to go false, regardless of what they are set to. Hopefully that is clear enough, 6 individual options and one all options selection, selection any of the individual options unselects the all options check box and the all options box unselects all individual option.

Thanks

**ChemistB** · 07-30-2012, 02:59 PM

Sounds like you might want to use Option Buttons instead. Only one can be chosen, others will clear. Alternately, one can have a pseudo checkbox which is controlled by VBA and double clicking. See my attachment on Post #21 of this thread
http://www.excelforum.com/july-compe...move-tick.html
Is any of this helpfuL?

**ArchiveMike** · 07-30-2012, 03:15 PM

Chem, thanks for looking at this. Option buttons would be great, except I want the user to be able to select any or all of the options OR the all options button. There are six options the user can buy, or if they want them all, and only if they want them all, I will give them a bundled price.

**ChemistB** · 07-30-2012, 05:06 PM

Here's an example of a spreadsheet which uses the double click method. Double click to add or erase checkmarks. To view the code, right click the tab and "View code" (Thanks to BigBas for helping with this code).

**ArchiveMike** · 07-30-2012, 07:27 PM

Here is my specific spreadsheet. I won't add double click, appreciate the thought. If you click on all the options except the OnDemand, you will see pricing populated. When you select the the OnDemand option, you will see the pricing for everything else disappear. I am doing it with math but it would be better and less confusing to just clear all the options above when I click OnDemand, though I can work with this as is. Any other suggestions would be welcome, I am not an Excel user...

Thanks

**ChemistB** · 07-31-2012, 09:43 AM

Okay, I created two short macros. Assign the first macro to your "Base Product" checkbox (Checkbox38_click) and the second macro (CheckboxIndOp_check) to each of the other checkboxes.

Sub CheckBox38_Click()
' code for clearing
If Range("A4") = True Then Range("A9:A14").Value = False
End Sub

Sub CheckBoxIndOp_Click()
'For individual Options
If WorksheetFunction.CountIf(Range("A9:A14"), True) > 0 Then
Range("A4").Value = False
End If
End Sub

To assign the macro, right click on the checkbox (unprotected spreadsheet) and assign macro> new. Then paste the two macros into the open module. Then right click on the individual checkboxes and assign them to the IndOp macro.
Did that work for you?