Copy/Paste cell if checkbox selected

[SeanBean]

I have a spreadsheet with form control checkboxes next to various cells. I would like a formula where if the checkbox is selected, then it copies a specific cell and pastes it into a range on the same sheet (I4:I15). There will be multiple checkboxes checked so I would need it to copy/paste the cell corresponding to that checkbox to the row below the cell in that range (I4:I15) with information already in it. If possible, I would also like it that if the cell is checked and then un-checked, it would delete the information pasted into that range from that check box. The items that will be copy/pasted will be text. All items are on the same Excel Sheet "Sheet1"

I appreciate any help given!

For example:

• If CheckBox1 is selected, copy cell C72
  • Paste contents of cell C72 into range I4:I15. If I4 has information in it, check I5, if I5 is empty, paste contents of C72 into I5
• If CheckBox2 is selected, copy cell C73
  • Paste contents of cell C73 into range I4: I15. If I4 has information in it, check I5, If I5 has information in it, check I6, if I6 is empty, paste contents of C73 into I6
• If CheckBox1 is unselected, delete the pasted information from C72 from range I4:I15

 

[kevin9999]

Please try the following on a copy of your workbook. Put the code in a standard module, and assign the macro to all your checkboxes. In the absence of any layout shown it assumes the checkbox is in the cell immediately to the right of value to be copied/deleted to/from I4:I15.

Option Explicit
Sub Change_Check_Boxes()
    Dim cx As Excel.CheckBox, s As String
    Set cx = ActiveSheet.CheckBoxes(Application.Caller)
    s = cx.TopLeftCell.Offset(0, -1)
    If cx.Value = xlOn Then
        Range("I" & Application.Max(Cells(Rows.Count, 9).End(xlUp).Offset(1).Row, 4)) = s
    Else
        Dim i As Long
        For i = 4 To 15
            If Cells(i, 9) = s Then
                Cells(i, 9).Delete shift:=xlUp
                Exit For
            End If
        Next i
    End If
End Sub

 

[kevin9999]

Link to demo file with the above code.

 

[SeanBean]

Please try the following on a copy of your workbook. Put the code in a standard module, and assign the macro to all your checkboxes. In the absence of any layout shown it assumes the checkbox is in the cell immediately to the right of value to be copied/deleted to/from I4:I15.

Option Explicit
Sub Change_Check_Boxes()
    Dim cx As Excel.CheckBox, s As String
    Set cx = ActiveSheet.CheckBoxes(Application.Caller)
    s = cx.TopLeftCell.Offset(0, -1)
    If cx.Value = xlOn Then
        Range("I" & Application.Max(Cells(Rows.Count, 9).End(xlUp).Offset(1).Row, 4)) = s
    Else
        Dim i As Long
        For i = 4 To 15
            If Cells(i, 9) = s Then
                Cells(i, 9).Delete shift:=xlUp
                Exit For
            End If
        Next i
    End If
End Sub

That worked! Thanks!!!!!!!!

 

[kevin9999]

Happy to help, and welcome to the board