Use Checkbox to copy data to new sheet

SisterSue

New Member
Joined
Apr 8, 2014
Messages
1
Hi,

Thank you in advance for your time! I followed the instructions from this post: http://www.mrexcel.com/forum/excel-questions/401291-checkbox-copy-row-new-sheet.html

Unfortunately, I am not having success.

Goal: When user selects check box, copy a cell and paste the value in a new sheet. Delete the data if the cell is unchecked. Allow this for multiple checkboxes (so there is no specific paste range. new data is continually added below previously pasted data).

When I use the following code, the paste works correctly; however, it does not Delete the data if the box is unchecked:
Code:
Private Sub CheckBox1_Click()
    With Sheets("LATE")
        ckrownum = .Cells(Rows.Count, "B").End(xlUp).Row
        If CheckBox1 Then
            Range("B6").Copy   '(Obviously you'll need to adjust this range to your needs
            .Range("B" & ckrownum).PasteSpecial
        Else
            For x = 1 To ckrownum
                If .Cells(ckrownum, 2) = Cells(1, 2) Then
                    .Rows(ckrownum).Delete
                    Exit For
                End If
            Next
        End If
    End With
End Sub

Also, I tried to use the following code to use a handler. However, I get an error on the line "If Controls": "Compile Error. Sub or Function Not Defined."

Code:
Private Sub CheckBox1_Change()
  Call CheckBox_Handler(1)
End Sub

Private Sub CheckBox_Handler(arg1)
'===================================================================
' Arg1 is the variable containing the chkbox # that was changed
'===================================================================
    With Sheets("LATE")
        ckrownum = .Cells(Rows.Count, "B").End(xlUp).Row
        If Controls("CheckBox" & arg1) Then     'if arg1 equals 1, then this will make
                                                'Checkbox1 the control
            Range("B6" & arg1).Copy   '(Obviously you'll need to adjust this range to your needs
            .Range("B" & ckrownum).PasteSpecial
        Else
            For x = 1 To ckrownum
                If .Cells(ckrownum, 2) = Cells(arg1, 2) Then
                    .Rows(ckrownum).Delete
                    Exit For
                End If
            Next
        End If
    End With
End Sub

Note, I am a novice user! I would appreciate any assistance! I'm happy to reach the goal in another manner; however, the simpler the better! Thanks so much.
 

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)

Forum statistics

Threads
1,223,155
Messages
6,170,403
Members
452,325
Latest member
BlahQz

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top