Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Checkbox to select image | Excel Programming | |||
when select cell in range checkbox value is true_but how? | Excel Programming | |||
Cannot select checkbox to delete it from a spreadsheet | Excel Discussion (Misc queries) | |||
How can I select one checkbox and have it unselect other ones? | Excel Worksheet Functions | |||
Select checkbox with tab key? | Excel Programming |