click on checkbox copy to active cell

C

Cheryl

Is there any way to click on a checkbox beside a row of information and copy
that row of data to an active cell?
 
D

Dave Peterson

Maybe.

If your active cell is in column A, you can copy that whole row to the
activecell's row.

If you use a checkbox from the forms toolbar, you can add as many as you want to
the worksheet. But assign each of them the same common macro:

Option Explicit
Sub testme01()

Dim myCBX As CheckBox
Dim myCopyObjectsWithCells As Boolean

Set myCBX = ActiveSheet.CheckBoxes(Application.Caller)

If myCBX.Value = xlOn Then
If myCBX.TopLeftCell.Row = ActiveCell.Row Then
'do nothing
Else
myCopyObjectsWithCells = Application.CopyObjectsWithCells
Application.CopyObjectsWithCells = False
myCBX.TopLeftCell.EntireRow.Copy _
Destination:=ActiveSheet.Cells(ActiveCell.Row, "A")
With Application
.CutCopyMode = False
.CopyObjectsWithCells = myCopyObjectsWithCells
End With
End If
End If

End Sub

But there might be a better way of doing what you really want. This seems a
little overkill to me.
 
C

Cheryl

What about if I don't want to copy the whole row.. but just a range of cells
beside the checkbox... ? I tried to substitute Range for Entirerow (since I
want to copy a range of information but it is not always in the same spot)
and just specify the columns.. but that wouldn't work..

Cheryl said:
Thank you.. once again.. u may have saved my life...


Dave Peterson said:
Maybe.

If your active cell is in column A, you can copy that whole row to the
activecell's row.

If you use a checkbox from the forms toolbar, you can add as many as you want to
the worksheet. But assign each of them the same common macro:

Option Explicit
Sub testme01()

Dim myCBX As CheckBox
Dim myCopyObjectsWithCells As Boolean

Set myCBX = ActiveSheet.CheckBoxes(Application.Caller)

If myCBX.Value = xlOn Then
If myCBX.TopLeftCell.Row = ActiveCell.Row Then
'do nothing
Else
myCopyObjectsWithCells = Application.CopyObjectsWithCells
Application.CopyObjectsWithCells = False
myCBX.TopLeftCell.EntireRow.Copy _
Destination:=ActiveSheet.Cells(ActiveCell.Row, "A")
With Application
.CutCopyMode = False
.CopyObjectsWithCells = myCopyObjectsWithCells
End With
End If
End If

End Sub

But there might be a better way of doing what you really want. This
Click to expand...
seems
a
little overkill to me.
Click to expand...
and
Click to expand...
 
D

Dave Peterson

Here's one way:

Option Explicit
Sub testme01()

Dim myCBX As CheckBox
Dim myCopyObjectsWithCells As Boolean
Dim myRngToCopy As Range

Set myCBX = ActiveSheet.CheckBoxes(Application.Caller)

If myCBX.Value = xlOn Then
If myCBX.TopLeftCell.Row = ActiveCell.Row Then
'do nothing
Else
myCopyObjectsWithCells = Application.CopyObjectsWithCells
Application.CopyObjectsWithCells = False

Set myRngToCopy _
= ActiveSheet.Cells(myCBX.TopLeftCell.Row, "b").Resize(1, 5)

myRngToCopy.Copy _
Destination:=ActiveSheet.Cells(ActiveCell.Row, "A")
With Application
.CutCopyMode = False
.CopyObjectsWithCells = myCopyObjectsWithCells
End With
End If
End If

End Sub

This is the line you'll want to adjust:

Set myRngToCopy _
= ActiveSheet.Cells(myCBX.TopLeftCell.Row, "b").Resize(1, 5)

I started in column B of that same row. Then I resized it to be 1 row by 5
columns (B:F).

You can start anywhere and resize to the number of columns you need.

And I pasted in column A with this portion:
Destination:=ActiveSheet.Cells(ActiveCell.Row, "A")

Adjust to suit.


What about if I don't want to copy the whole row.. but just a range of cells
beside the checkbox... ? I tried to substitute Range for Entirerow (since I
want to copy a range of information but it is not always in the same spot)
and just specify the columns.. but that wouldn't work..
Click to expand...
 
Top