Create a Button to select/deselect a range of checkboxes

SBNUT

New Member
Joined
Aug 25, 2021
Messages
33
Office Version
  1. 365
Platform
  1. Windows
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.
 

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.

JEC

Well-known Member
Joined
Aug 21, 2021
Messages
571
Office Version
  1. 365
Platform
  1. Windows
If you use form controls:
VBA Code:
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:
VBA Code:
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

New Member
Joined
Aug 25, 2021
Messages
1
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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

New Member
Joined
Aug 25, 2021
Messages
33
Office Version
  1. 365
Platform
  1. Windows
Thanks JEC, This worked great. Very simple but it worked like a champ.
 

AuburnMist

New Member
Joined
Sep 22, 2021
Messages
3
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

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

Well-known Member
Joined
Aug 21, 2021
Messages
571
Office Version
  1. 365
Platform
  1. Windows
To uncheck everything

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

or

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

AuburnMist

New Member
Joined
Sep 22, 2021
Messages
3
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

To uncheck everything

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

or

VBA Code:
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

Well-known Member
Joined
Aug 21, 2021
Messages
571
Office Version
  1. 365
Platform
  1. Windows
Try this

VBA Code:
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
 

Forum statistics

Threads
1,148,371
Messages
5,746,308
Members
424,006
Latest member
Metal_warrior

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top