Check if checkbox has linked cell or if it's #Ref

[Beneindias]

I have an excel file with table that has checkboxes for each row in column B.

When I delete a row, the checkbox that was there, looses it's linked cell.

So, is there any way that I can check if linked cell has a cell, or if it's an "#Ref" error?

I need to check if LinkedCell is an error, and if it is, delete that checkbox.

I was trying this code, but it doesn't work.

If IsError(Cbx.LinkedCell) Then
    Cbx.Delete
End If

Also tried this code, but this one gives me an error:

"Run-time error 438:
Object doesn't support this property or method"

If IsError(Cbx.FormControl.LinkedCell) Then
    Cbx.Delete
End If

 

[HongRu]

Try this.
The code will kill all the checkboxes with linkedcell= "#REF!".
Strongly suggest SAVE THE EXCEL FILE BEFORE RUNNING the code.

Sub Del_CheckBox()
    Dim myShapes As Shapes
    Dim e As Shape
    Dim myLinkedcell As String
    Set myShapes = ActiveSheet.Shapes
    On Error Resume Next
    For Each e In myShapes
        If Not IsError(e.OLEFormat.progID) Then
            If InStr(1, e.OLEFormat.progID, "CheckBox") > 0 Then
                myLinkedcell = e.OLEFormat.Object.LinkedCell
                If myLinkedcell = "#REF!" Then
                    e.Delete
                End If
            End If
        End If
    Next e
End Sub

 

[Beneindias]

Try this.
The code will kill all the checkboxes with linkedcell= "#REF!".
Strongly suggest SAVE THE EXCEL FILE BEFORE RUNNING the code.

Sub Del_CheckBox()
    Dim myShapes As Shapes
    Dim e As Shape
    Dim myLinkedcell As String
    Set myShapes = ActiveSheet.Shapes
    On Error Resume Next
    For Each e In myShapes
        If Not IsError(e.OLEFormat.progID) Then
            If InStr(1, e.OLEFormat.progID, "CheckBox") > 0 Then
                myLinkedcell = e.OLEFormat.Object.LinkedCell
                If myLinkedcell = "#REF!" Then
                    e.Delete
                End If
            End If
        End If
    Next e
End Sub

Thanks for your code.

I didn't try it yet, because I just figured out a solution, I was trying to use the IsError function, but it wasn't working, so, I had to campare if LinkedCell = #REF!, as in your solution and I had to add an ElseIf to the If condition that I previously had, who was checking if checkbox was outside of my table. I was trying to use IsError, because, in my mind, its a better way to do it, than to hardcode the error, but oh well...

Sub Checkboxes_Deletion()
    Dim lastRow As Long
    Dim Sh As Worksheet
    Dim worksheet1 As String: worksheet1 = "Salarios" 'Salarios
    Dim PagoColumn As String: PagoColumn = "B"
    Dim LastRowColumn As String: LastRowColumn = "l:l" 'Include Entire Column.
    Dim Cbx As CheckBox
     
           Set Sh = ActiveSheet
        
        With Sh
            'Número da última linha da tabela
            lastRow = WorksheetFunction.countA(Range(LastRowColumn))
        End With
    
        'Itera pelas Checkboxes da folha
        For Each Cbx In ActiveSheet.CheckBoxes
            'Checa se o espaço não está ocupado por uma checkbox. "lastRow + 8" para que seja considerada a linha após o fim da tabela
            If Not Intersect(Cbx.TopLeftCell, ActiveSheet.Range(PagoColumn & lastRow + 8)) Is Nothing Then
                'Apaga a Checkbox, se esta estiver na linha após a tabela
                Cbx.Delete
            'No caso de não estar após a tabela, este próximo código checa se a checkbox tem ligação a alguma célula
            '(quando a linha é apagada, a checkbox perde a ligação à célula e fica com erro #REF!)
            ElseIf Cbx.LinkedCell = "#REF!" Then
                Cbx.Delete
            End If
        Next Cbx
End Sub

That being said, I will make a copy of my file and try your code as soon as I have some free time.

Thanks again