Run time error 438 on a checkbox checking function

[vbaNumpty]

Found some code looking to check if a cell has a checkbox already or not in a worksheet, but I am getting a 438 error. Hoping someone could provide insight into what I am doing wrong.

 For y = 2 To x

                addMe.Offset(n, 1).value = findValue.Offset(y, 0).value 'product
                addMe.Offset(n, 2).value = findValue.Offset(y, 2).value 'cases
                addMe.Offset(n, 3).value = findValue.Offset(y, 3).value 'pack size
                addMe.Offset(n, 4).value = findValue.Offset(y, 4).value 'Staging
                addMe.Offset(n, 5).value = findValue.Offset(y, 5).value 'assortment
                addMe.Offset(n, 6).value = findValue.Offset(y, 6).value 'colour
                addMe.Offset(n, 7).value = findValue.Offset(y, 7).value 'cover
                addMe.Offset(n, 8).value = findValue.Offset(y, 8).value 'ornament
                addMe.Offset(n, 9).value = findValue.Offset(y, 9).value 'upc
                addMe.Offset(n, 10).value = findValue.Offset(y, 10).value 'caretag
                addMe.Offset(n, 11).value = findValue.Offset(y, 11).value 'insulation
                addMe.Offset(n, 12).value = findValue.Offset(y, 12).value 'sleeve
                addMe.Offset(n, 13).value = findValue.Offset(y, 13).value 'notes
                addMe.Offset(n, 14).value = findValue.Offset(y, 14).value 'box label
                
                MyLeft = addMe.Offset(n, 15).Left
                MyTop = addMe.Offset(n, 15).Top
                MyHeight = addMe.Offset(n, 15).Height
                MyWidth = addMe.Offset(n, 15).Width
                
                If HasCheckbox(addMe.Offset(n, 15)) Then GoTo line2
                
                ActiveSheet.CheckBoxes.Add(MyLeft, MyTop, MyWidth, MyHeight).Select
                With Selection
                    .Caption = ""
                    .value = xlOff
                    .LinkedCell = addMe.Offset(n, 15)
                    .Display3DShading = False
                    .Placement = xlFreeFloating
                    .PrintObject = True
                End With
                
line2:
                'formatting
                addMe.Offset(n, 1).VerticalAlignment = xlCenter

Public Function HasCheckbox(rng As Range) As Boolean
    If Not Application.Intersect(rng, ActiveSheet.CheckBoxes.TopLeftCell) Is Nothing Then
        HasCheckbox = True
    Else
         HasCheckbox = False
    End If
End Function

 

[Domenic]

You're going to need to loop through each checkbox, and then individually check whether it intersects with the specified range...

Public Function HasCheckbox(rng As Range) As Boolean

    Dim currentCheckbox As CheckBox
    For Each currentCheckbox In rng.Parent.CheckBoxes
        If Not Application.Intersect(rng, currentCheckbox.TopLeftCell) Is Nothing Then
            HasCheckbox = True
            Exit Function
        End If
    Next currentCheckbox

    HasCheckbox = False
    
End Function

Hope this helps!

 

[vbaNumpty]

You're going to need to loop through each checkbox, and then individually check whether it intersects with the specified range...

Public Function HasCheckbox(rng As Range) As Boolean

    Dim currentCheckbox As CheckBox
    For Each currentCheckbox In rng.Parent.CheckBoxes
        If Not Application.Intersect(rng, currentCheckbox.TopLeftCell) Is Nothing Then
            HasCheckbox = True
            Exit Function
        End If
    Next currentCheckbox

    HasCheckbox = False
  
End Function

Hope this helps!

I'll give it a shot, but I was calling the original function inside of a for loop. I thought it would loop each time and check already.


EDIT: Gave it a go and it works! Thanks. Not sure I understand the difference in logic though.

 

[Domenic]

TopLeftCell is a property of the CheckBox object, not the CheckBoxes collection. So we need to loop through each checkbox within the collection, and then check the TopLeftCell property for each one individually.

Cheers!