Select Cell on Same Row as Checkbox
 

I am trying to right a code that when the Copy Prior Line checkbox is
clicked, it copies the line two rows above and paste special (values
only) into the same line as the checkbox (i.e. copies line 27 into line
29). But I want to make one general macro that I can apply to every
Copy Prior Line checkbox, but I seem to have to use a line number...
Please help!

Also, can I apply this macro to every checkbox? Thank you and I really
appreciate the help...


Private Sub CheckBox2_Click()

Dim k As Integer

If CheckBox2.Value = True Then

Application.ScreenUpdating = False

With Range("B27:B300")

For k = 29 To 300


If Range("B" & k) = "True" Then
Range("B" & k - 2, "BI" & k - 2).Select
Range("B" & k - 2, "BI" & k - 2).Copy
Range("B" & k, "BI" & k).Select
Selection.PasteSpecial xlPasteValues
Application.CutCopyMode = False
Range("B" & k).Select

Else: GoTo 10
End If


Next k

10

End With

End If

End Sub

Select Cell on Same Row as Checkbox
 

Assuming Checkboxes from the control toolbox toolbar:

? Activesheet.OleObjects("Checkbox1").TopLeftCell.ro w
13

gives you the row number.

You might look at the code by John Walkenback showing how to use a class
module to provide event code for multiple controls. Although written for
commandbuttons on a userform, it would work as well for checkboxes on a
worksheet.

http://www.j-walk.com/ss/excel/tips/tip44.htm

--
Regards,
Tom Ogilvy

wrote in message
s.com...
I am trying to right a code that when the Copy Prior Line checkbox is
clicked, it copies the line two rows above and paste special (values
only) into the same line as the checkbox (i.e. copies line 27 into line
29). But I want to make one general macro that I can apply to every
Copy Prior Line checkbox, but I seem to have to use a line number...
Please help!

Also, can I apply this macro to every checkbox? Thank you and I really
appreciate the help...


Private Sub CheckBox2_Click()

Dim k As Integer

If CheckBox2.Value = True Then

Application.ScreenUpdating = False

With Range("B27:B300")

For k = 29 To 300


If Range("B" & k) = "True" Then
Range("B" & k - 2, "BI" & k - 2).Select
Range("B" & k - 2, "BI" & k - 2).Copy
Range("B" & k, "BI" & k).Select
Selection.PasteSpecial xlPasteValues
Application.CutCopyMode = False
Range("B" & k).Select

Else: GoTo 10
End If


Next k

10

End With

End If

End Sub