CODEWITHSUNDEEP
vbaexcel
A.J
A.J

Reputation: 215

Select CheckBoxes in Excel using VBA

I have a excel sheet having check boxes from Range "D12" to "D26", on click of abutton I want to select all checkboxes but based on only range i.e. all checkboxes in Range("D12:D26").

Code which I am using is below, but not working for me:

Private Sub SelectALL_Click()

    Dim cells As Range
    Dim rng As Range

    Set rng = Sheet1.Range("D12:D14")



    For Each cells In rng
        cells.Select
    Next


End Sub

Upvotes: 1

Views: 5265

Answers (1)

Siddharth Rout
Siddharth Rout

Reputation: 149277

Since you have not mentioned what kind of control is it (ActiveX or Form Control), take your pick

Form Control Example

Sub FormControl_Example()
    Dim shp As Shape
    Dim rng As Range, rngShp As Range

    With ThisWorkbook.Sheets("Sheet1") '<~~ Change this to the relevant sheet name
        Set rng = .Range("D12:D14")

        For Each shp In .Shapes
            Set rngShp = .Range(shp.TopLeftCell.Address)

            If Not Intersect(rngShp, rng) Is Nothing Then
                shp.OLEFormat.Object.Value = True
            End If
        Next
    End With
End Sub

ActiveX Control Example

Sub ActiveX_Example()
    Dim shp As Shape
    Dim rng As Range, rngShp As Range

    With ThisWorkbook.Sheets("Sheet1") '<~~ Change this to the relevant sheetname
        Set rng = .Range("D12:D14")

        For Each shp In .Shapes
            Set rngShp = .Range(shp.TopLeftCell.Address)

            If Not Intersect(rngShp, rng) Is Nothing Then
                .OLEObjects(shp.Name).Object.Value = True
            End If
        Next
    End With
End Sub

Upvotes: 4

Related Questions