Copying an existing row which also copies the checkbox with its code

Question

My table must grow by 1 row when I touch a button. but also the code for a checkbox, which must be copied. That doesn't work this way, so how do I do it? The mistake is on the bolded line.

Private Sub CommandButton2_Click()
    Dim lastRow As Long
    Dim lastColumn As Long
    Dim chkBox As CheckBox
'Determine the last row in the active worksheet
    lastRow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
'Determine the last column you want to expand
    lastColumn = 7
'Insert a row above the last row
    Rows(lastRow + 1).Insert
'Copy the formulas from the last row to the new row
    Range(Cells(lastRow, 1), Cells(lastRow, lastColumn)).Copy Range(Cells(lastRow + 1, 1), Cells(lastRow + 1, lastColumn))
'Copy the last checkbox with his VBA code
    Set chkBox = ActiveSheet.CheckBoxes(ActiveSheet.CheckBoxes.Count)
    chkBox.Copy
    ActiveSheet.CheckBoxes.Add(chkBox.Left, chkBox.Top + chkBox.Height + 5, chkBox.Width, chkBox.Height).Select
    ActiveSheet.Paste
End Sub

Answer 1

You can make a new checkbox object based on the old one rather than cloning the existing one. It is not possible to utilise the Copy method on a CheckBox object in VBA. SubcommandButton2 Click, private () dim final As long as dim last, row One Long Dim Column as CheckBox chkBox dark new Dim chkBoxTop As Double ChkBox As CheckBox

    'Determine the last row in the active worksheet
    lastRow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
    
    'Determine the last column you want to expand
    lastColumn = 7
    
    'Insert a row above the last row
    Rows(lastRow + 1).Insert
    
    'Copy the formulas from the last row to the new row
    Range(Cells(lastRow, 1), Cells(lastRow, lastColumn)).Copy Range(Cells(lastRow + 1, 1), Cells(lastRow + 1, lastColumn))
    
    'Create a new checkbox based on the last one
    Set chkBox = ActiveSheet.CheckBoxes(ActiveSheet.CheckBoxes.Count)
    chkBoxTop = chkBox.Top + chkBox.Height + 5
    Set newChkBox = ActiveSheet.CheckBoxes.Add(chkBox.Left, chkBoxTop, chkBox.Width, chkBox.Height)
    newChkBox.Caption = chkBox.Caption
    newChkBox.Value = chkBox.Value
    
    'Copy the checkbox code behind it
    newChkBox.OnAction = chkBox.OnAction
    
End Sub

Create a new checkbox based on the last checkbox, set its caption and value to be the same, and copy the code behind it.