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:
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."
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.
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.