CODEWITHSUNDEEP
excelvba
user3673417
user3673417

Reputation: 189

Excel VBA - Ckeck if cell contains a checkbox

I am using the following script to add checkboxes to a range of cells. I would like to enhance this by checking if the cell already contains a checkbox and if it does then do not add a new checkbox in the cell, ie only add a check box to a cell in the range if it does not already contain a checkbox.

    LRow = ActiveSheet.Range("D" & Rows.Count).End(xlUp).Row

For cell = 10 To LRow
        CLeft = Cells(cell, "R").Left
        CTop = Cells(cell, "R").Top
        CHeight = Cells(cell, "R").Height
        CWidth = Cells(cell, "R").Width
        ActiveSheet.CheckBoxes.Add(CLeft, CTop, CWidth, CHeight).Select
        With Selection
            .Caption = ""
            .Value = xlOff
            .Display3DShading = False
        End With
Next cell

Upvotes: 1

Views: 4109

Answers (1)

ttaaoossuu
ttaaoossuu

Reputation: 7884

Use this function to check if a range contains a checkbox:

Public Function HasCheckbox(rng As Range) As Boolean
    For Each CB In ActiveSheet.CheckBoxes
        If Not Application.Intersect(rng, CB.TopLeftCell) Is Nothing Then
            HasCheckbox = True
            Exit Function
        End If
    Next CB
    HasCheckbox = False
End Function

Upvotes: 4

Related Questions