Form Control Checkbox to Copy and Paste Text from

Question

I am looking for a way to copy and paste text from one sheet to another when I tick a form control checkbox and to delete it when I uncheck it. At the moment the macro I have written does nothing, it doesn't come up with any errors it just doesn't work. What I have so far is:

Sub CheckBox3_Click()    

Application.ScreenUpdating = False


If CheckBox3 = True Then

Sheets("Data Sheet").Activate
Range("B1").Select
Selection.Copy
Sheets("Sheet1").Select
Range("C1").Select
ActiveSheet.Paste

Application.ScreenUpdating = True

End If

If CheckBox3 = False Then

ActiveSheet.Range("C1").Select
Selection.Delete

End If

End Sub

Any help would be appreciated, thanks.

Answer 1

To overcome your Runtime error please change your code to

ActiveSheet.Range("B1").Select

and do the same for the target range C1

However, the much more elegant way is to get rid of Select and Activate all together by using VBA rather than "macro recording" ... work with Range objects which will simplify your code, you avoid messy screen jumps etc ...

Private Sub CheckBox1_Click()
Dim SrcRange As Range, TrgRange As Range

    Set SrcRange = Worksheets("Data Sheet").[B1]
    Set TrgRange = Worksheets("Sheet1").[C1]

    If CheckBox1 Then
        TrgRange = SrcRange
    Else
        TrgRange = ""
    End If
End Sub