Delete checkboxes from empty rows

[Jakezer]

Hi,
I want to delete the checkboxes on the empty rows (am testing on the row B; for exp; if B6 is empty then delete checkbox in E6)
Now the code is working except for it is deleting all the checkboxes not only the empty rows ones
Btw, I can't combine the two in conditions with and & ?

Dim n As Long
Dim k As Long
Dim cl As Range

For Each cl In Range("B2:B25")
k = k + 1
If IsEmpty(cl) Then
If HasCheckbox(Range("E1").Offset(k, 0)) = True Then
cb.Delete
End If
n = n + 1
End If
Next cl

 

[EXCEL MAX]

You can try this...

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

    Dim cl As Range

    For Each cl In Range("B2:B25")
        If IsEmpty(cl) Then _
            Call HasCheckbox(cl.Offset(, 3))
    Next cl

End Sub

Function HasCheckbox(ByVal vR As Range)

    Dim vO As OLEObject, vS As Shape

    For Each vO In ActiveSheet.OLEObjects
        If vO.progID Like "*CheckBox*" Then
             Set vS = Shapes(vO.Name)
             If vS.TopLeftCell.Row = vR.Row Then
                vS.Delete
            End If
        End If
    Next vO

End Function

 

[Jakezer]

Thanks for replying,
Sorry if i forgot to mention but; the thing is I am using the Control type of checkboxes not the Active X that's why I dont think oleobject works for me;
I am using a code called checkboxes to add checkboxes, and a function called HasCheckbox to test if my cell has a checkbox already or not;
That first code worked for me except for it's deleting all checkboxes, is there way to use on only range B2:B25 please?
I can paste the checkboxes codes here if needed

 

[EXCEL MAX]

Let me see the code...

 

[Jakezer]

Here:

I think i can name these checkboxes by adding
cb.Name = "Checkbox" & i
But I didnt, they're being auto-named

The code that's adding checkboxes for me:

Sub checkboxes(rng As Range)
        CLeft = rng.Left
        CTop = rng.Top
        CHeight = rng.Height
        CWidth = rng.Width
        Set cb = Worksheets("Sheet1").checkboxes.Add(CLeft, CTop, CWidth, CHeight)
        cb.Characters.Text = ""
        cb.Value = xlOff
        cb.Display3DShading = False
End Sub

The code that's testing if a cell already has a checkbox or not:

Public Function HasCheckbox(rng As Range) As Boolean
    For Each cb In Worksheets("Sheet1").checkboxes
        If Not Application.Intersect(rng, cb.TopLeftCell) Is Nothing Then
            HasCheckbox = True
            Exit Function
        End If
    Next cb
    HasCheckbox = False
End Function

 

[EXCEL MAX]

I think you don't need to modify code too much.
Just declare variable as checkbox outside of the code.
Try in this way...

Dim cb As CheckBox

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

    Dim n As Long
    Dim k As Long
    Dim cl As Range

    For Each cl In Range("B2:B25")
        k = k + 1
        If IsEmpty(cl) Then
            If HasCheckbox(Range("E1").Offset(k, 0)) = True Then
                cb.Delete
            End If
            n = n + 1
        End If
    Next cl

End Sub

Public Function HasCheckbox(rng As Range) As Boolean

    For Each cb In Worksheets("Sheet1").checkboxes
        If Not Application.Intersect(rng, cb.TopLeftCell) Is Nothing Then
            HasCheckbox = True
            Exit Function
        End If
    Next cb
    HasCheckbox = False

End Function

 

[Jakezer]

Idk why I can't even execute that code, tried everything it doesn't when executing like other code to choose it
Also I already have Dim cb As Object above, and no changes when I changed it to CheckBox instead of Object, when tried the code without

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

and

End Sub

Just this part inside my code

Dim n As Long
    Dim k As Long
    Dim cl As Range

    For Each cl In Range("B2:B25")
        k = k + 1
        If IsEmpty(cl) Then
            If HasCheckbox(Range("E1").Offset(k, 0)) = True Then
                cb.Delete
            End If
            n = n + 1
        End If
    Next cl

It still deletes all checkboxes even the ones where cell B of their rows Is not empty

 

[EXCEL MAX]

You can try this one too.
Inesert this code in the module and run procedure for deleting.
Go step by step through debugger with small example of data.
Be sure you not have some conflicting events.

Dim vC As Shape

Private Sub DeleteCheckboxes()

    Dim cl As Range

    For Each cl In Range("B2:B25")
        If IsEmpty(cl) And HasCheckbox(cl.Offset(, 3)) Then _
            vC.Delete
    Next cl

End Sub


Function HasCheckbox(ByVal vR As Range)

    For Each vC In ActiveSheet.Shapes
        If vC.Type = msoFormControl Then
            If vC.FormControlType = xlCheckBox Then
                If vC.TopLeftCell.Row = vR.Row Then
                    HasCheckbox = True
                    Exit Function
                End If
            End If
        End If
    Next vC
    
End Function