Using Macros to untick checkboxes in specified ranges

[csbaer]

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

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

 

[Fluff]

Hi & welcome to MrExcel.
For col B, how about

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]

Hi & welcome to MrExcel.
For col B, how about

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]

How about

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]

How about

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

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]

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]

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]

Do i have to use 'intersect' to stop that from happening?

There are probably other ways, but that's how I would do it.

 

[csbaer]

There are probably other ways, but that's how I would do it.

Brilliant, got it to work - thanks for all your help!

 

[Fluff]

Glad you sorted it & thanks for the feedback.