Can we create dependent 'Check Boxes' under Form Controls in Excel 2007? If so, how?

Question

I want to create a set of 'check boxes' and I want one of them to have overall control over all others - so when you check it, every other check box gets checked, if you uncheck it, then you can check individual check boxes as needed. For example, if I have a set of data such as: 'All Years' and then 2001, 2002, 2003, 2004. If the checkbox corresponding to 'All years' is checked, then, I want rest of the four checkboxes to be checked as well. If not, then, I want to be able to individually check / uncheck the remaining four checkboxes. Is something like this possible in Excel 2007? This is for the visual aspect - I can already make the resulting data (TRUE/FALSE) to act as needed - but visually, I cannot make it work. Any ideas out there?

Answer 1

Here is one way with a VBA macro.



Copy this macro to the clipboard:



Sub CheckAll()

If [IV1].Value = 0 Then

ActiveSheet.CheckBoxes = xlOn

[IV1].Value = 1

Exit Sub

ElseIf [IV1].Value = 1 Then

ActiveSheet.CheckBoxes = xlOff

[IV1].Value = 0

End If

End Sub





Next, right click on the sheet tab that your check boxes are in.



Select 'View Code'



Paste the macro into the module editing space to the right.



Close back to Excel.



Right click on the 'All Years' checkbox and select 'Assign Macro'.



Assign this macro the the checkbox.



Close back to Excel.



Click the All Years checkbox and all other checkboxes will be checked. Click it again and they all will uncheck.



Note: if you protect your sheet, make sure that cell IV1 is not locked. Also hide column IV to prevent printing if necessary.



Thanks for the tip, Cosmosis.

Answer 2

exciting, i'm a Weeping Willow. Weeping Willow, the melancholy. fascinating yet crammed with melancholy, alluring, very empathic, loves something fascinating and tasteful, likes to return and forth, dreamer, under pressure, capricious, trouble-free, may be prompted yet isn't undemanding to stay with, annoying, solid instinct, suffers in love yet unearths sometimes an anchoring better half.

Answer 3

Is there a way to disable the other checkboxes?