Button to check/uncheck table column checkboxes VBA

[johnwill8258]

For my table of column cells, I'd like the checkbox(form control) in each cell to be checked/unchecked with a click of a macro button (form control).

i.e,
for (each table column H cells checkbox)
if (not checked)
check
else
uncheck

From search, I found that people use the name of the checkboxes in the language to check/uncheck. However, since I do not want checkbox name visible, I deleted those. The checkboxes are individually linked to respective column cell.

Since I am uncertain if there will be other type of checkboxes included, I do not want all checkboxes in the activesheet to check/uncheck with activated button; only the column H checkboxes.

I'd appreciate any help with this.
Thank you!

 

[Domenic]

Try assigning the following macro to your button...

[color=darkblue]Sub[/color] ToggleCheckBoxes()
    [color=darkblue]Dim[/color] wks [color=darkblue]As[/color] Worksheet
    [color=darkblue]Dim[/color] oCheckBox [color=darkblue]As[/color] CheckBox
    
    [color=darkblue]Set[/color] wks = Worksheets("Sheet1") [color=green]'change the sheet name accordingly[/color]
    
    [color=darkblue]For[/color] [color=darkblue]Each[/color] oCheckBox [color=darkblue]In[/color] wks.CheckBoxes
        [color=darkblue]If[/color] [color=darkblue]Not[/color] Intersect(oCheckBox.TopLeftCell, wks.Columns("H")) [color=darkblue]Is[/color] [color=darkblue]Nothing[/color] [color=darkblue]Then[/color]
            [color=darkblue]If[/color] oCheckBox.Value = xlOff [color=darkblue]Then[/color]
                oCheckBox.Value = xlOn
            [color=darkblue]Else[/color]
                oCheckBox.Value = xlOff
            [color=darkblue]End[/color] [color=darkblue]If[/color]
        [color=darkblue]End[/color] [color=darkblue]If[/color]
    [color=darkblue]Next[/color] oCheckBox
[color=darkblue]End[/color] [color=darkblue]Sub[/color]

Hope this helps!

 

[L. Howard]

Delete, solution provided...

H

 

[johnwill8258]

Try assigning the following macro to your button...

[COLOR=darkblue]Sub[/COLOR] ToggleCheckBoxes()
    [COLOR=darkblue]Dim[/COLOR] wks [COLOR=darkblue]As[/COLOR] Worksheet
    [COLOR=darkblue]Dim[/COLOR] oCheckBox [COLOR=darkblue]As[/COLOR] CheckBox
    
    [COLOR=darkblue]Set[/COLOR] wks = Worksheets("Sheet1") [COLOR=green]'change the sheet name accordingly[/COLOR]
    
    [COLOR=darkblue]For[/COLOR] [COLOR=darkblue]Each[/COLOR] oCheckBox [COLOR=darkblue]In[/COLOR] wks.CheckBoxes
        [COLOR=darkblue]If[/COLOR] [COLOR=darkblue]Not[/COLOR] Intersect(oCheckBox.TopLeftCell, wks.Columns("H")) [COLOR=darkblue]Is[/COLOR] [COLOR=darkblue]Nothing[/COLOR] [COLOR=darkblue]Then[/COLOR]
            [COLOR=darkblue]If[/COLOR] oCheckBox.Value = xlOff [COLOR=darkblue]Then[/COLOR]
                oCheckBox.Value = xlOn
            [COLOR=darkblue]Else[/COLOR]
                oCheckBox.Value = xlOff
            [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
        [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
    [COLOR=darkblue]Next[/COLOR] oCheckBox
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]

Hope this helps!

Thank you very much that worked for me!

If its not too much of trouble, could you help me assigning a similar macro, except this one will:
if (all checkboxes not checked in column H)
check
else
uncheck all

 

[Domenic]

Try...

[color=darkblue]Sub[/color] SetCheckBoxes()
    [color=darkblue]Dim[/color] ws [color=darkblue]As[/color] Worksheet
    [color=darkblue]Dim[/color] oCheckBox [color=darkblue]As[/color] CheckBox
    [color=darkblue]Dim[/color] aCheckBoxes() [color=darkblue]As[/color] [color=darkblue]String[/color]
    [color=darkblue]Dim[/color] lChkBxCnt [color=darkblue]As[/color] [color=darkblue]Long[/color]
    [color=darkblue]Dim[/color] bAtLeastOneNotChecked [color=darkblue]As[/color] [color=darkblue]Boolean[/color]
    
    [color=darkblue]Set[/color] ws = Worksheets("Sheet1")
    
    lChkBxCnt = 0
    [color=darkblue]For[/color] [color=darkblue]Each[/color] oCheckBox [color=darkblue]In[/color] ws.CheckBoxes
        [color=darkblue]If[/color] [color=darkblue]Not[/color] Intersect(oCheckBox.TopLeftCell, ws.Columns("H")) [color=darkblue]Is[/color] [color=darkblue]Nothing[/color] [color=darkblue]Then[/color]
            lChkBxCnt = lChkBxCnt + 1
            [color=darkblue]ReDim[/color] [color=darkblue]Preserve[/color] aCheckBoxes(1 [color=darkblue]To[/color] lChkBxCnt)
            aCheckBoxes(lChkBxCnt) = oCheckBox.Name
            [color=darkblue]If[/color] oCheckBox.Value = xlOff [color=darkblue]Then[/color] bAtLeastOneNotChecked = [color=darkblue]True[/color]
        [color=darkblue]End[/color] [color=darkblue]If[/color]
    [color=darkblue]Next[/color] oCheckBox
    
    [color=darkblue]If[/color] bAtLeastOneNotChecked [color=darkblue]Then[/color]
        ws.CheckBoxes(aCheckBoxes).Value = xlOn
    [color=darkblue]Else[/color]
        ws.CheckBoxes(aCheckBoxes).Value = xlOff
    [color=darkblue]End[/color] [color=darkblue]If[/color]
    
    [color=darkblue]Set[/color] ws = [color=darkblue]Nothing[/color]
    [color=darkblue]Set[/color] oCheckBox = [color=darkblue]Nothing[/color]
[color=darkblue]End[/color] [color=darkblue]Sub[/color]

Hope this helps!

 

[johnwill8258]

Try...

[COLOR=darkblue]Sub[/COLOR] SetCheckBoxes()
    [COLOR=darkblue]Dim[/COLOR] ws [COLOR=darkblue]As[/COLOR] Worksheet
    [COLOR=darkblue]Dim[/COLOR] oCheckBox [COLOR=darkblue]As[/COLOR] CheckBox
    [COLOR=darkblue]Dim[/COLOR] aCheckBoxes() [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]String[/COLOR]
    [COLOR=darkblue]Dim[/COLOR] lChkBxCnt [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Long[/COLOR]
    [COLOR=darkblue]Dim[/COLOR] bAtLeastOneNotChecked [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Boolean[/COLOR]
    
    [COLOR=darkblue]Set[/COLOR] ws = Worksheets("Sheet1")
    
    lChkBxCnt = 0
    [COLOR=darkblue]For[/COLOR] [COLOR=darkblue]Each[/COLOR] oCheckBox [COLOR=darkblue]In[/COLOR] ws.CheckBoxes
        [COLOR=darkblue]If[/COLOR] [COLOR=darkblue]Not[/COLOR] Intersect(oCheckBox.TopLeftCell, ws.Columns("H")) [COLOR=darkblue]Is[/COLOR] [COLOR=darkblue]Nothing[/COLOR] [COLOR=darkblue]Then[/COLOR]
            lChkBxCnt = lChkBxCnt + 1
            [COLOR=darkblue]ReDim[/COLOR] [COLOR=darkblue]Preserve[/COLOR] aCheckBoxes(1 [COLOR=darkblue]To[/COLOR] lChkBxCnt)
            aCheckBoxes(lChkBxCnt) = oCheckBox.Name
            [COLOR=darkblue]If[/COLOR] oCheckBox.Value = xlOff [COLOR=darkblue]Then[/COLOR] bAtLeastOneNotChecked = [COLOR=darkblue]True[/COLOR]
        [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
    [COLOR=darkblue]Next[/COLOR] oCheckBox
    
    [COLOR=darkblue]If[/COLOR] bAtLeastOneNotChecked [COLOR=darkblue]Then[/COLOR]
        ws.CheckBoxes(aCheckBoxes).Value = xlOn
    [COLOR=darkblue]Else[/COLOR]
        ws.CheckBoxes(aCheckBoxes).Value = xlOff
    [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
    
    [COLOR=darkblue]Set[/COLOR] ws = [COLOR=darkblue]Nothing[/COLOR]
    [COLOR=darkblue]Set[/COLOR] oCheckBox = [COLOR=darkblue]Nothing[/COLOR]
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]

Hope this helps!

You are awesome. Thanks for the help :D

 

[Domenic]

You're very welcome!

Cheers!