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, Mar 19 '20 at 13:52
Don't you need to check the intersection of `rng` and `xcheckbox`? — SJR, Mar 19 '20 at 13:56
Also I recommend not to use `ActiveCell` and don't use `.Select` see [How to avoid using Select in Excel VBA](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba). This can easily fail and is not very reliable. — Pᴇʜ, Mar 19 '20 at 14:05
I tried changing the intersection values and i'm getting error. how do i write the exact code? — Yusuf, Mar 19 '20 at 14:09

Pᴇʜ · Accepted Answer · 2020-03-19T14:25:04.050

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 '20 at 14:25

answered Mar 19 '20 at 14:10

Pᴇʜ

56,719
10
49
73

This code is still selecting other checkboxes in the sheet too ! – Yusuf Mar 19 '20 at 14:16
@Yusuf check my answer again – Pᴇʜ Mar 19 '20 at 14:17
Its the same problem again, it keeps selecting all the checkboxes in other columns. Do we also have to define the column? – Yusuf Mar 19 '20 at 14:24
@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. – Pᴇʜ Mar 19 '20 at 14:27
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.. – Yusuf Mar 19 '20 at 14:41
GOT IT.. It was the linked cells which were causing the other boxes to follow the range !.. thanks ALOT for your help !!! – Yusuf Mar 19 '20 at 14:43

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

1 Answers1