Macro - Checkbox triggers copy/paste

[HNelshp]

Hello! Background: I mostly use Excel for text tables. I can use the Add function, but beyond that ... not so good with formulas. I was given a project that requires more knowledge than I have and could obtain from the websites I've perused. I was hoping you could help.

I have Yes and No checkboxes created in a table. I would like the action of checking "No" to trigger text to be copied from cells on the current spreadsheet to cells on another spreadsheet. Is that doable? So...if someone clicks the "No" checkbox in E7, the text from Spreadsheet 1 A7 and B7 would be automatically copied and pasted onto Spreadsheet 2, A7 and B7...and likewise down the line. "No" in E8 would copy A8 and B8 from Spreadsheet 1 to 2 and so on.

Any help you could give me on this would be greatly appreciated.

Thanks, Heather

 

[nightcrawler23]

This should be doable via vba. Are the checkboxes form controls or active x controls (i mean how have they been inserted in the sheet)

 

[HNelshp]

I entered as Form Controls. I don't think my boss would mind either way, so that could always be changed. (She knows even less than me...so its easy to impress her. LOL)

 

[nightcrawler23]

haha... nice one....

 

[HNelshp]

So how much time and effort would it take someone to do via VBA? And is it something a novice could do?

 

[nightcrawler23]

if u link your checkbox to its corr cell in col z then every time you check or uncheck the box you wd get a true or false respectively. this should trigger the below code which is to be put in your worksheet(Not module).

But somehow it is not working. Can someone else help.

Private Sub Worksheet_Change(ByVal Target As Range)
Set isect = Application.Intersect(Range("Z1:Z100"), Target)
If Not (isect) Is Nothing Then
    If Target = "True" Then
        Range("A" & Target.Row & ":B" & Target.Row).Copy Destination:=Sheets("Sheet2").Range("A" & Target.Row)
    End If
End If

End Sub