Excel Checkbox VBA Help - Master checkbox to tick all checkboxes in a range

[ClaireD]

Hi there,

I'm having some problems with a macro in Excel. I have a sheet full of checkboxes. I need to put in multiple "master checkboxes". By selecting a master checkbox I would like to tick all checkboxes in a given range of cells.

The first master checkbox is in E11 (Check Box 894). the range of checkboxes it needs to control are in cells E12 onwards. There will be this set up in each column of the spreadsheet (about 20 or so).

Currently I have the following code but this is turning all of the checkboxes on and off. I can't work out how to select just a few checkboxes instead of all of them.

Sub SelectAll_Click()
Dim CB As CheckBox
For Each CB In ActiveSheet.CheckBoxes
  If CB.Name <> ActiveSheet.CheckBoxes("Check Box 894").Name Then
    CB.Value = ActiveSheet.CheckBoxes("Check Box 894").Value
  End If
Next CB
End Sub

 

[MickG]

Perhaps something like:-
Alter for rows/Columns

Sub SelectAll_Click()
Dim CB As CheckBox
For Each CB In ActiveSheet.CheckBoxes
  If CB.Name <> ActiveSheet.CheckBoxes("Check Box 894").Name Then
    If CB.TopLeftCell.Row > 11 Then
        CB.Value = ActiveSheet.CheckBoxes("Check Box 894").Value
    End If
  End If
Next CB
End sub

 

[ClaireD]

Perhaps something like:-
Alter for rows/Columns

Sub SelectAll_Click()
Dim CB As CheckBox
For Each CB In ActiveSheet.CheckBoxes
  If CB.Name <> ActiveSheet.CheckBoxes("Check Box 894").Name Then
    If CB.TopLeftCell.Row > 11 Then
        CB.Value = ActiveSheet.CheckBoxes("Check Box 894").Value
    End If
  End If
Next CB
End sub

Hi Mick,

thanks for getting back to me so quickly. How can I adjust this for my desired range? For example column E from E12 onwards??

 

[MickG]

try:-

Sub SelectAll_Click()
Dim CB As CheckBox
For Each CB In ActiveSheet.CheckBoxes
  If CB.Name <> ActiveSheet.CheckBoxes("Check Box 894").Name Then
    If CB.TopLeftCell.Row > 11 Or CB.TopLeftCell.Column > 4 Then
        CB.Value = ActiveSheet.CheckBoxes("Check Box 894").Value
     End If
   End If
Next CB
End Sub

 

[ClaireD]

Thanks! That worked perfectly

 

[MickG]

You're welcome