Create a Button to select/deselect a range of checkboxes

[SBNUT]

I have a spreadsheet that has over 800 check boxes split into two columns. One set of check boxes is in column P and one set is in column R. I would like to create a button that will just either check all the checkboxes in column P or uncheck all the checkboxes in column P without affecting the checkboxes in column R. I can do this for the entire sheet, but am having trouble just limiting it to column P. I know just enough about VBA to be dangerous, so any help would be greatly appreciated. Currently these are all forms control checkboxes but I could change them to ActiveX checkboxes if needed.

 

[JEC]

If you use form controls:

Sub tst()
 With ActiveSheet
    For Each cb In .CheckBoxes
      If cb.Left < .Columns(17).Left Then cb.Value = False
    Next
 End With
End Sub

If you use ActiveX:

Sub tst2()
   For Each cb In ActiveSheet.OLEObjects
      If InStr(cb.Name, "CheckBox") And cb.Left < ActiveSheet.Columns(17).Left Then cb.Object = False
    Next
End Sub

 

[mjones095]

This will do the trick. Just make sure each checkbox is truly located in the the P column.

Dim ws As Excel.Worksheet
Sub Toggle_Checkboxes_Col_P()
Dim check_box As CheckBox
Set ws = ThisWorkbook.Worksheets("Sheet1")

For Each check_box In ws.CheckBoxes

If Split(Cells(1, check_box.TopLeftCell.Column).Address, "$")(1) = "P" Then

If check_box.Value = xlOn Then
check_box.Value = False
Else
check_box.Value = True
End If

End If

Next check_box

End Sub

 

[SBNUT]

Thanks JEC, This worked great. Very simple but it worked like a champ.

 

[AuburnMist]

Hello! I have been looking online for hours for a solution like this, and this is the closest I could find, however, there is a slight modification that I require. I hope someone can assist me.
The code above is exactly what I need, however, I do not want it to toggle between True and False. I only need the code to result in all checkboxes to change to False (unchecked).
Can you please provide a code as above without toggling back and forth?

Thanks

 

[JEC]

To uncheck everything

Sub tst2()
   For Each cb In ActiveSheet.OLEObjects
      If InStr(cb.Name, "CheckBox")  Then cb.Object = False
    Next
End Sub

or

Sub tst()
    For Each cb In ActiveSheet.CheckBoxes
      cb.Value = False
    Next
End Sub

 

[AuburnMist]

To uncheck everything

Sub tst2()
   For Each cb In ActiveSheet.OLEObjects
      If InStr(cb.Name, "CheckBox")  Then cb.Object = False
    Next
End Sub

or

Sub tst()
    For Each cb In ActiveSheet.CheckBoxes
      cb.Value = False
    Next
End Sub

Thanks Jec! However, I need it only to respond to a specific column (in my case, "F").

 

[JEC]

Try this

Sub tst2()
 With ActiveSheet
   For Each cb In .OLEObjects
      If InStr(cb.Name, "CheckBox") And cb.Left >= .Columns(6).Left And cb.Left <= .Columns(7).Left Then cb.Object = False
    Next
 End With
End Sub

 

[AuburnMist]

Perfect!! This is it! Thank you very much.