CODEWITHSUNDEEP
excelvbacheckbox
user3514907
user3514907

Reputation: 85

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

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.

Upvotes: 1

Views: 1879

Answers (1)

MikeD
MikeD

Reputation: 8941

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

Upvotes: 1

Related Questions