Reputation: 85
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
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