Using a button to select all check boxes (with a wrinkle)

[philryan29]

I have seen a few tutorials that show how to select all check boxes by selecting a single check box, but I am trying to perform this action by clicking a button and am struggling to adapt the instructions. Also, I would like to perform this action only on check boxes where the related row is not blank.

Example, A1:A3 each contain a check box. B1 and B2 have values, but B3 is blank. I would like to be able to click a button and select only A1 and A2. In actual use, I would likely have 200 rows, each with a check box, and anywhere from 0-199 of the rows may be blank any given time the sheet is loaded.

Any help would be much appreciated!

 

[Domenic]

Are the checkboxes form controls? Or are they ActiveX controls? If they're the former, try...

[color=darkblue]Option[/color] [color=darkblue]Explicit[/color]

[color=darkblue]Sub[/color] SelectCheckBoxes()

    [color=darkblue]Dim[/color] ws [color=darkblue]As[/color] Worksheet
    [color=darkblue]Dim[/color] oChBx [color=darkblue]As[/color] CheckBox
    
    [color=darkblue]Set[/color] ws = Worksheets("Sheet1") [color=green]'change the sheet name accordingly[/color]
    
    [color=darkblue]For[/color] [color=darkblue]Each[/color] oChBx [color=darkblue]In[/color] ws.CheckBoxes
        [color=darkblue]With[/color] oChBx
            [color=darkblue]If[/color] [color=darkblue]Not[/color] Intersect(ws.Columns("A"), .TopLeftCell) [color=darkblue]Is[/color] [color=darkblue]Nothing[/color] [color=darkblue]Then[/color]
                [color=darkblue]If[/color] Len(.TopLeftCell.Offset(, 1)) > 0 [color=darkblue]Then[/color]
                    .Value = [color=darkblue]True[/color]
                [color=darkblue]Else[/color]
                    .Value = [color=darkblue]False[/color]
                [color=darkblue]End[/color] [color=darkblue]If[/color]
            [color=darkblue]End[/color] [color=darkblue]If[/color]
            
        [color=darkblue]End[/color] [color=darkblue]With[/color]
    [color=darkblue]Next[/color] oChBx
    
[color=darkblue]End[/color] [color=darkblue]Sub[/color]

Hope this helps!