VBA to Check/Uncheck Checkboxes

[Madraykin]

Hello guys, I've written some VBA to control some checkboxes but it's causing an issue that I can't quite get my head around fixing and was wondering if anyone here had any ideas.

So basically I have a stacked bar chart. I have a list of options (about 20) that can be individually clicked to see how these options will affect the chart (financially). These 20 options are also grouped into 'Packages'. So I've written some code (example below) to make it so that when Package 1 is selected, options 1, 2, 3 and 4 automatically have their boxes checked visually. The checkbox for Package 1 is linked to AA9, and AA10 displays the word 'YES' when AA9 reads TRUE

Private Sub Worksheet_Calculate()
If Range("AA10").Value = "YES" ThenActiveSheet.CheckBoxes("STLHM1").Value = xlOn
ActiveSheet.CheckBoxes("ATLHM").Value = xlOn
ActiveSheet.CheckBoxes("SAHM1").Value = xlOn
ActiveSheet.CheckBoxes("STLHM2").Value = xlOn
ActiveSheet.CheckBoxes("SAHM2").Value = xlOn
ActiveSheet.CheckBoxes("SAHM3").Value = xlOn
Else
ActiveSheet.CheckBoxes("STLHM1").Value = xlOff
ActiveSheet.CheckBoxes("ATLHM").Value = xlOff
ActiveSheet.CheckBoxes("SAHM1").Value = xlOff
ActiveSheet.CheckBoxes("STLHM2").Value = xlOff
ActiveSheet.CheckBoxes("SAHM2").Value = xlOff
ActiveSheet.CheckBoxes("SAHM3").Value = xlOff
End If
End Sub

The issue that I'm having is that I now need to be able to deselect individual checkboxes from the 20 long list to 'tailor' the packages. But because I have this code running I'm unable to do this. I was thinking it was something along the line of having this as an 'on-click' event for the checkbox rather than a worksheet_calculate - would this be correct thinking?

Any help appreciated, I'm tearing my hair out!!!!

Thanks guys

Mads

 

[dmt32]

Working like a dream - Dave you are a genius. One day I hope to be well versed enough in this stuff that I can help people too!!!

Many thanks for you kind feedback, it is very much appreciated & glad solution does what you want.

Compliments of the season to you

Dave