Creating a macro which Select/ Unselect All checkboxes within a variable range as defined

Question

The macro is intended to find the location of the main checkbox (application.caller) and then select all the checkboxes within that column up to 14 rows below it. My sheet has various checkboxes elsewhere, the current macro checks ALL the checkboxes in the sheet. What Am I doing wrong?

Sub SelectAll_Click()
'Select / Clear All macro
Dim xCheckBox As CheckBox, n As Variant, rng As Range, loc As Range, loc1 As Range
'Application.Caller.Name
n = ActiveSheet.CheckBoxes(Application.Caller).Name

With ActiveSheet

Set loc = ActiveSheet.CheckBoxes(Application.Caller).TopLeftCell
'Set loc1 = loc.Address
'MsgBox loc1
loc.Select
Set rng = Range(loc.Address, ActiveCell.Offset(13, 0))
MsgBox (rng.Address)
End With




For Each xCheckBox In Application.ActiveSheet.CheckBoxes
'MsgBox (xCheckBox.TopLeftCell.Address)
'With xCheckBox.Select
   If Not Intersect(loc, rng) Is Nothing Then

        If xCheckBox.Name <> Application.ActiveSheet.CheckBoxes(n).Name Then
       ' If Not Intersect(rngShp, rng) Is Nothing Then

        xCheckBox.Value = Application.ActiveSheet.CheckBoxes(n).Value
        End If

    End If
    'End With

    Next
    End Sub

HI @SJR, thanks for your comment , could you please be more specific? — Yusuf
– Yusuf, Commented Mar 19, 2020 at 13:52
Don't you need to check the intersection of rng and xcheckbox? — SJR
– SJR, Commented Mar 19, 2020 at 13:56
Also I recommend not to use ActiveCell and don't use .Select see How to avoid using Select in Excel VBA. This can easily fail and is not very reliable. — Pᴇʜ
– Pᴇʜ, Commented Mar 19, 2020 at 14:05
I tried changing the intersection values and i'm getting error. how do i write the exact code? — Yusuf
– Yusuf, Commented Mar 19, 2020 at 14:09

Pᴇʜ · Accepted Answer · 2020-03-19 14:25:04Z

2

The following is true for all checkboxes that are not the checkbox that you clicked on.

If xCheckBox.Name <> Application.ActiveSheet.CheckBoxes(n).Name Then
    xCheckBox.Value = Application.ActiveSheet.CheckBoxes(n).Value
End If

You need to check additionally if the xCheckBox.TopLeftCell intersects with your desired range rng. So it is only true for all other checkboxes which TopLeftCell is within the range:

If xCheckBox.Name <> Application.ActiveSheet.CheckBoxes(n).Name And Not Intersect(xCheckBox.TopLeftCell, rng) Is Nothing Then
    xCheckBox.Value = Application.ActiveSheet.CheckBoxes(n).Value
End If

Finally your code could look something like this:

Option Explicit

Public Sub SelectAll_Click()
    Dim ws As Worksheet
    Set ws = ActiveSheet

    Dim MasterChkBox As CheckBox
    Set MasterChkBox = ws.CheckBoxes(Application.Caller)


    Dim MasterLocation As Range
    Set MasterLocation = ws.CheckBoxes(Application.Caller).TopLeftCell

    Dim Rng As Range
    Set Rng = MasterLocation.Resize(RowSize:=14)

    'MsgBox (Rng.Address)

    Dim xCheckBox As CheckBox
    For Each xCheckBox In ws.CheckBoxes
        If xCheckBox.Name <> MasterChkBox.Name And _
           Not Intersect(xCheckBox.TopLeftCell, Rng) Is Nothing Then

            xCheckBox.Value = MasterChkBox.Value

        End If
    Next xCheckBox
End Sub

^{Image 1: If you check the master box it will only select the 13 boxes below the master box.}

edited Mar 19, 2020 at 14:25

answered Mar 19, 2020 at 14:10

Pᴇʜ

57.9k10 gold badges55 silver badges75 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Yusuf Over a year ago

This code is still selecting other checkboxes in the sheet too !

Pᴇʜ Over a year ago

@Yusuf check my answer again

Yusuf Over a year ago

Its the same problem again, it keeps selecting all the checkboxes in other columns. Do we also have to define the column?

Pᴇʜ Over a year ago

@Yusuf you must be doing something wrong. See the animation I added. It affects only the 14 boxes, but no other boxes in other columns or other rows than these 14.

Yusuf Over a year ago

Im confused why is it not producing the same result on my sheet. Ive copy-pasted the exact same code.. but it keeps selecting other boxes too..

|

Collectives™ on Stack Overflow

Creating a macro which Select/ Unselect All checkboxes within a variable range as defined

1 Answer 1

6 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related