Form Control Checkbox to Copy and Paste Text from a Different Sheet

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.

Please add the `Sub ...()` definition to your code so we know what kind of proc that is (e.g. which event and which object it relates to, etc.) — MikeD, Feb 26 '15 at 09:50
first problem: you cannot reference Form Controls like this ... replace the Checkbox by an ActiveX Control ... see also http://stackoverflow.com/questions/6932084/how-to-access-checkbox-from-vba-in-excel-2007 — MikeD, Feb 26 '15 at 09:59
I have replaced the Checkbox with an ActiveX Control box and I get an run-time error "Select method of Range class failed" on the line 'Range("B1").Select'. — user3514907, Feb 26 '15 at 10:02
sorry I was anyhow to fast ... in 2010 this seems to work ... checking ... — MikeD, Feb 26 '15 at 10:04

MikeD · Accepted Answer · 2015-02-26T10:48:26.540

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

Form Control Checkbox to Copy and Paste Text from a Different Sheet

1 Answers1

Linked