lundi 6 janvier 2020

Add a checkbox if a cell is not empty

I have a range of cells on C1:C400 that change content according to an initial choice from a list. Sometimes, a cell can be empty depending on the choice made. I need to add a checkbox to the right of a cell is the cell is not empty, in D column. And as well, delete the checkbox when the cell gets empty. This is what I did so far, I don't get any error but it doesn't do anything :

Private Sub Worksheet_Change(ByVal Target As Range)
Dim wsTarget As Worksheet
Dim oCheckBox As CheckBox
Dim cell As Range

Set wsTarget = ActiveSheet
Set isect = Application.Intersect(Range("C1:C400"), Target)

For Each cell In isect(Target, Range("C1:C400"))
    If Not isect Is Nothing Then
'If the cell is NOT empy, I should add a checkbox, to the right of the cell without text
    With wsTarget
        If Not IsEmpty(cell.Value) Then
            Set oCheckBox = ActiveSheet.CheckBoxes.Add(cell.Left, cell.Top, cell.Width, cell.Height)
                With chkbox
                    .Text = ""
        Else
            For Each chkbox In ActiveSheet.CheckBoxes
                    If Not Intersect(cell, chkbox.TopLeftCell) Is Nothing Then
                        chkbox.Delete
        End If
    End With
    Else
    End If
End Sub

Does anybody have a clue on what to do ?




Aucun commentaire:

Enregistrer un commentaire