Creating checkbox with VBA for each new table row

[Beneindias]

Hello excel experts.

I'm trying to create a checkbox in column B, linked to column C, for each new row that I create in a table.

I have this VBA code that I'm testing and its a mix of recorded macro and code found on the internet:

Sub ButtonClick10()
Dim lastRow As Long
    Dim sh As Worksheet
  
    Set sh = Sheets("Salarios")
  
    With sh
        lastRow = .Cells(Rows.Count, "D").End(xlUp).Row
    End With
  
    With sh.Range("B" & lastRow)
        With sh.CheckBoxes.Add(20.25, 147, 72, 72)
            .Caption = ""
            .Locked = False
            .LockedText = False
            .Value = xlOff
            .LinkedCell = Range("C", lastRow)
        End With
    End With
End Sub

this line:
.LinkedCell = Range("C", lastRow) is givin an error.

This code is working with a button just to test purposes, because the goal is to have it working every time I add a row to the table.

This code is creating the cell in the wrong place and not linked to the cell in column C.

How do I make this code work every time a row is added to that table?
How to properly create the checkbox in the right place?
And will it delete that checkbox if I delete a row?
In the print screen that I attached, I should have the checkbox in row 11, but it was created in row 12, with no cell linked.


Thanks to every one

 

[ZMyrrh]

Sub ButtonClick10()
Dim lastRow As Long
Dim sh As Worksheet
Dim worksheet1 As String: worksheet1 = "Sheet1" 'Salarios
Dim PagoColumn As String: PagoColumn = "J"
Dim StatusColumn As String: StatusColumn = "k"
Dim LastRowColumn As String: LastRowColumn = "l:l" 'Include Entire Column.
 
    Set sh = Sheets(worksheet1)
 
    With sh
        lastRow = WorksheetFunction.CountA(Range(LastRowColumn))
    End With
 
    With sh.Range(PagoColumn & lastRow)
        With sh.CheckBoxes.Add(Cells(lastRow, PagoColumn).Left, Cells(lastRow, PagoColumn).Top, 10, 10)
            .Caption = ""
            .Locked = False
            .LockedText = False
            .Value = xlOff
            .LinkedCell = Cells(lastRow, StatusColumn).Address
        End With
    End With
End Sub

Tell me if this works for you! There are some variables that you might need to change.

 

[Beneindias]

Sub ButtonClick10()
Dim lastRow As Long
Dim sh As Worksheet
Dim worksheet1 As String: worksheet1 = "Sheet1" 'Salarios
Dim PagoColumn As String: PagoColumn = "J"
Dim StatusColumn As String: StatusColumn = "k"
Dim LastRowColumn As String: LastRowColumn = "l:l" 'Include Entire Column.
 
    Set sh = Sheets(worksheet1)
 
    With sh
        lastRow = WorksheetFunction.CountA(Range(LastRowColumn))
    End With
 
    With sh.Range(PagoColumn & lastRow)
        With sh.CheckBoxes.Add(Cells(lastRow, PagoColumn).Left, Cells(lastRow, PagoColumn).Top, 10, 10)
            .Caption = ""
            .Locked = False
            .LockedText = False
            .Value = xlOff
            .LinkedCell = Cells(lastRow, StatusColumn).Address
        End With
    End With
End Sub

Tell me if this works for you! There are some variables that you might need to change.

Hello @ZMyrrh, thanks for your help

The checkbox was created, but it was placed in the wrong row.
I changed the variables, to work with my file