Looping through checkboxes - HELP NEEDED

Hi

In my spreadsheet, I have 30 x rows of checkboxes with 12 x checkboxes on each row.

On each row, one checkbox is used as a master control to select / de-select all the other eleven checkboxes. The ‘master’ checkboxes are all logically named from “MasterL01” on Line 1 to “MasterL30” on Line 30

On each line, the other eleven checkboxes are named logically as follows:

Line 1: “L01CB01”, “L01CB02”, “L01CB03” up to “L01CB11”
Line 2: “L02CB01”, “L02CB02”, “L02CB03” up to “L02CB11”
Line 3: “L03CB01”, “L03CB02”, “L03CB03” up to “L03CB11”
etc

To each of the ‘master’ checkboxes I have assigned a macro such as this one, assigned to “MasterL01”:

Sub Master_L01()

‘ This macro is used to select all / deselect all checkboxes on Line 1

ActiveSheet.CheckBoxes("L01CB01").Value = ActiveSheet.CheckBoxes("MasterL01").Value
ActiveSheet.CheckBoxes("L01CB02").Value = ActiveSheet.CheckBoxes("MasterL01").Value
ActiveSheet.CheckBoxes("L01CB03").Value = ActiveSheet.CheckBoxes("MasterL01").Value
ActiveSheet.CheckBoxes("L01CB04").Value = ActiveSheet.CheckBoxes("MasterL01").Value
ActiveSheet.CheckBoxes("L01CB05").Value = ActiveSheet.CheckBoxes("MasterL01").Value
ActiveSheet.CheckBoxes("L01CB06").Value = ActiveSheet.CheckBoxes("MasterL01").Value
ActiveSheet.CheckBoxes("L01CB07").Value = ActiveSheet.CheckBoxes("MasterL01").Value
ActiveSheet.CheckBoxes("L01CB08").Value = ActiveSheet.CheckBoxes("MasterL01").Value
ActiveSheet.CheckBoxes("L01CB09").Value = ActiveSheet.CheckBoxes("MasterL01").Value
ActiveSheet.CheckBoxes("L01CB10").Value = ActiveSheet.CheckBoxes("MasterL01").Value
ActiveSheet.CheckBoxes("L01CB11").Value = ActiveSheet.CheckBoxes("MasterL01").Value

End Sub

As you can see, I am clearly not a programmer(!) and have written this code in a very simplistic, line-by-line fashion. I know there is a better way to write this code – looping through each of the checkboxes – but I have no idea how to do this. Any help, or advice, would be most welcome!

Sample file attached...

Many thanks
Mike

Hi Arkadi - thanks for the advice. I will definitely make sure that I use the

Please Login or Register  to view this content.

tags in future. You can tell I'm new at this can't you?!

I'm afraid I don't really understand your reply but will try to work it out. Like I said, I'm not a programmer!

One thing point to note is that each master check box should only affect the other checkboxes on the same row - so I think I will probably need 30 macros or will your suggested code allow for that?

Hello again Arkadi! I hope all is well in Smiths Falls, Ontario - any signs of Spring yet or are you still frozen up?

I'm sorry not to have replied sooner but, for various reasons, I had to put this project on hold but now it has been resurrected.

I have implemented your solution and it works just beautifully, thanks. So, now I have just one macro assigned to all the 'Master' check boxes and it behaves exactly as you described. Simple, elegant, just what the doctor ordered! so HUGE THANKS!

But now, I have another challenge! What I want to do is this:

WHEN clicking a checkbox whose name starts with "L01" CHECK the value of ALL "L01" checkboxes in the sheet
IF the value of ALL "L01" checkboxes = TRUE
THEN set the value of the Checkbox MasterL01 as TRUE
ELSE set the value of the Checkbox MasterL01 as FALSE

In this way, if I manually uncheck one or more "L01" check boxes, the Master will be unchecked. If I then make all "L01" boxes = TRUE, then the Master will be set as TRUE.

The same logic would have to apply for all lines i.e. "L01", "L02", "L03" etc

My feeble brain is struggling to work out the code required to do this but hope you might be able to help (again)!

Many thanks and I look forward to hearing from you!

Cheers
Mike

p.s. I'm a bit of an aviation geek and follow one of your countrymen on YouTube "Flight Chops". Have you heard of him?