Using Macros to untick checkboxes in specified ranges

csbaer

New Member
Joined
Aug 29, 2020
Messages
5
Office Version
  1. 2016
Platform
  1. Windows
Hello!

I'm using the VBA code below to uncheck boxes in my worksheet, and I've assigned it to a shape so when I click it, all checkboxes are unticked.

However, since there are separate sections within the worksheet, I want separate macros that untick checkboxes in different ranges, i.e. one to untick checkboxes in column B, another for column C, etc.

Is this possible?

I
VBA Code:
Sub ClearCheckBoxes()
'Updateby Extendoffice
    Dim chkBox As Excel.CheckBox
    Application.ScreenUpdating = False
    For Each chkBox In ActiveSheet.CheckBoxes
            chkBox.Value = xlOff
    Next chkBox
    Application.ScreenUpdating = True
End Sub
 

Some videos you may like

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
52,960
Office Version
  1. 365
Platform
  1. Windows
Hi & welcome to MrExcel.
For col B, how about
VBA Code:
Sub ClearCheckBoxes()
   Dim chkBox As Excel.CheckBox
   Application.ScreenUpdating = False
   For Each chkBox In ActiveSheet.CheckBoxes
      If chkBox.TopLeftCell.Column = 2 Then chkBox.Value = xlOff
   Next chkBox
   Application.ScreenUpdating = True
End Sub
 

csbaer

New Member
Joined
Aug 29, 2020
Messages
5
Office Version
  1. 2016
Platform
  1. Windows
Hi & welcome to MrExcel.
For col B, how about
VBA Code:
Sub ClearCheckBoxes()
   Dim chkBox As Excel.CheckBox
   Application.ScreenUpdating = False
   For Each chkBox In ActiveSheet.CheckBoxes
      If chkBox.TopLeftCell.Column = 2 Then chkBox.Value = xlOff
   Next chkBox
   Application.ScreenUpdating = True
End Sub

Thank you for the welcome, and thanks so much for this! Is there also a way to specify selections within VBA? Say I want untick checkboxes within the range B2:B10?
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
52,960
Office Version
  1. 365
Platform
  1. Windows
How about
VBA Code:
Sub ClearCheckBoxes()
   Dim chkBox As Excel.CheckBox
   Application.ScreenUpdating = False
   For Each chkBox In ActiveSheet.CheckBoxes
      If Not Intersect(chkBox.TopLeftCell, Range("B2:B10")) Is Nothing Then chkBox.Value = xlOff
   Next chkBox
   Application.ScreenUpdating = True
End Sub
 

csbaer

New Member
Joined
Aug 29, 2020
Messages
5
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

How about
VBA Code:
Sub ClearCheckBoxes()
   Dim chkBox As Excel.CheckBox
   Application.ScreenUpdating = False
   For Each chkBox In ActiveSheet.CheckBoxes
      If Not Intersect(chkBox.TopLeftCell, Range("B2:B10")) Is Nothing Then chkBox.Value = xlOff
   Next chkBox
   Application.ScreenUpdating = True
End Sub
Thanks a lot! I just used
VBA Code:
If Not chkBox.TopLeftCell.Range("B5:B18") Is Nothing Then chkBox.Value = xlOff
and it got me what I wanted :) thanks again for the help!
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
52,960
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback.
The code you are using will remove the check from every checkbox, not just those in B5:B18
 

csbaer

New Member
Joined
Aug 29, 2020
Messages
5
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

You're welcome & thanks for the feedback.
The code you are using will remove the check from every checkbox, not just those in B5:B18
Oh pants, that's not what I want! Do i have to use 'intersect' to stop that from happening?
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
52,960
Office Version
  1. 365
Platform
  1. Windows
Glad you sorted it & thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,600
Messages
5,597,113
Members
414,125
Latest member
iQQ

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