click on checkbox copy to active cell

  • Thread starter Thread starter Cheryl
  • Start date Start date
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?
 
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.
 
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...
 
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...
 
Back
Top