worksheet change event to add CheckBox and edit Caption

[padadof2]

I'm trying to set up a worksheet change event that creates a checkbox in column 1 if the cell in column 8 changes. I am able to get a checkbox, but I can't get the caption to change. I'm able to do it using this code, but I don't want to do it manually. Thank you in advance for any pointers and help

Dim c As Range
    For Each c In Selection
        Dim cb As checkbox
        Set cb = ActiveSheet.CheckBoxes.Add(c.Offset(0, -7).Left, _
                                    c.Offset(0, -7).Top, _
                                    c.Offset(0, -7).Width, _
                                    c.Offset(0, -7).Height)
        With cb
            .Caption = "Proposal Sent"
            .Value = xlOff
            .Display3DShading = False
        End With
    Next

 

[padadof2]

I figured it out.
Here is the code in case someone else has the same issue

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim rngWatch As Range
    Dim cb As checkbox

    Set rngWatch = Range("H:H")

    If Intersect(rngWatch, Target) Is Nothing Then Exit Sub

    If Target.Count > 1 Then Exit Sub

    Set cb = ActiveSheet.CheckBoxes.Add(Target.Offset(0, -7).Left, _
        Target.Offset(0, -7).Top, _
        Target.Offset(0, -7).Width, _
        Target.Offset(0, -7).Height)
    
    With cb
        .Caption = "Proposal Sent"
        .Value = xlOff
        .Display3DShading = False
    End With

End Sub

 

[GWteB]

Alternatively you could use ...

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Target.CountLarge > 1 Then
        If Not Application.Intersect(Target, Columns("H:H")) Is Nothing Then
            Dim cb As CheckBox
            Set cb = Target.Parent.CheckBoxes.Add(10, 10, 10, 10)
            With Target.Offset(0, -7)
                cb.Caption = "Proposal Sent"
                cb.Left = .Left
                cb.Top = .Top
                cb.Height = .Height
                cb.Width = .Width
                ' cb.Width = 20 + (Len(cb.Caption) * 4)   ' << adjust width so text of caption is likely to fit
            End With
        End If
    End If
End Sub

 

[padadof2]

Alternatively you could use ...

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Target.CountLarge > 1 Then
        If Not Application.Intersect(Target, Columns("H:H")) Is Nothing Then
            Dim cb As CheckBox
            Set cb = Target.Parent.CheckBoxes.Add(10, 10, 10, 10)
            With Target.Offset(0, -7)
                cb.Caption = "Proposal Sent"
                cb.Left = .Left
                cb.Top = .Top
                cb.Height = .Height
                cb.Width = .Width
                ' cb.Width = 20 + (Len(cb.Caption) * 4)   ' << adjust width so text of caption is likely to fit
            End With
        End If
    End If
End Sub

thank you! I will try them both. Appreciate it.

 

[GWteB]

You're welcome and thanks for letting me know