jeudi 19 mars 2020

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

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



Aucun commentaire:

Enregistrer un commentaire