I have created a macro to copy and paste certain rows in a worksheet. These rows contain check boxes. I would like the macro to also copy the check boxes

When I create the macro to copy and paste the rows, the check boxes are also copied. However, when I run the macro, the check boxes are not created(?).

Is this possible? Any ideas? Anyone? Anyone?

Melanie,

I tried the macro record approach and the chk boxes were copied/pasted
when I ran it, however, rather than pasting the chkbox in my new row
it was pasted over the top of the existing checkbox. Try deleting or
moving the original textbox - I'm guessing you'll find another one
underneath.

You may find the following interesting. It inserts a checkbox in
column "H" on the current row when the user types something in column
3 of that row.

Depending how your copy/paste macro looks like you should be able to
modify this to suit. The key thing is to set the top of the checkbox
object to the top of the row that you are in.

If you have difficulties please post back your code and I'll see what
I can do.

Good luck,
Andrew

Private Sub Worksheet_Change(ByVal Target As Excel.Range)

Dim rng As Range
Dim obj As Object

If Target.Count 1 Then Exit Sub
If Target.Column = 3 Then
If IsEmpty(Target) Then Exit Sub
Set rng = Cells(Target.Row, "H")
' Check if there is already a checkbox
For Each obj In ActiveSheet.OLEObjects
'If TypeOf obj.Object Is MSForms.CheckBox Then
If obj.TopLeftCell.Address = rng.Address Then
Exit Sub
End If
'End If
Next

With ActiveSheet.OLEObjects.Add( _
ClassType:="Forms.CheckBox.1", _
Link:=False, _
DisplayAsIcon:=False, _
Left:=rng.Left + rng.Width / 2 - 10, _
Top:=rng.Top, _
Width:=rng.Width, _
Height:=rng.Height)
.Object.Caption = ""
'.LinkedCell = rng.Address
.Object.Value = False
End With

Application.ScreenUpdating = True
End If

End Sub



"Melanie" wrote in message ...
I have created a macro to copy and paste certain rows in a worksheet. These rows contain check boxes. I would like the macro to also copy the check boxes.

When I create the macro to copy and paste the rows, the check boxes are also copied. However, when I run the macro, the check boxes are not created(?).

Is this possible? Any ideas? Anyone? Anyone?

Thanks for the reply Andrew

The following is the macro I created. It does not contain any checkbox objects in the code
I'm not a VB guru so any assistance you can provide would be great. I'm assuming I woul
add the code you forwarded to me and replace variables with values where appropriate(?)

Thanks in advance
Melani

Sub AddInputCard(

' AddInputCard Macr
' Macro recorded 12/23/2003 by male

' Keyboard Shortcut: Ctrl+

Cells.Find(What:="Input(s)", After:=ActiveCell, LookIn:=xlFormulas,
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:=False).Activat
Range("A107").Selec
Selection.EntireRow.Inser
Selection.EntireRow.Inser
Selection.EntireRow.Inser
Selection.EntireRow.Inser
Selection.EntireRow.Inser
Selection.EntireRow.Inser
Selection.EntireRow.Inser
Selection.EntireRow.Inser
Selection.EntireRow.Inser
Selection.EntireRow.Inser
Selection.EntireRow.Inser
Selection.EntireRow.Inser
Selection.EntireRow.Inser
Selection.EntireRow.Inser
Selection.EntireRow.Inser
Selection.EntireRow.Inser
Selection.EntireRow.Inser
Selection.EntireRow.Inser
Rows("89:106").Selec
Selection.Cop
Range("A107").Selec
ActiveSheet.Past
ActiveWindow.SmallScroll Down:=1
Range("A107").Selec
Application.CutCopyMode = Fals
End Su

----- Andrew wrote: ----

Melanie

I tried the macro record approach and the chk boxes were copied/paste
when I ran it, however, rather than pasting the chkbox in my new ro
it was pasted over the top of the existing checkbox. Try deleting o
moving the original textbox - I'm guessing you'll find another on
underneath

You may find the following interesting. It inserts a checkbox i
column "H" on the current row when the user types something in colum
3 of that row

Depending how your copy/paste macro looks like you should be able t
modify this to suit. The key thing is to set the top of the checkbo
object to the top of the row that you are in

If you have difficulties please post back your code and I'll see wha
I can do

Good luck
Andre

Private Sub Worksheet_Change(ByVal Target As Excel.Range

Dim rng As Rang
Dim obj As Objec

If Target.Count 1 Then Exit Su
If Target.Column = 3 The
If IsEmpty(Target) Then Exit Su
Set rng = Cells(Target.Row, "H"
' Check if there is already a checkbo
For Each obj In ActiveSheet.OLEObject
'If TypeOf obj.Object Is MSForms.CheckBox The
If obj.TopLeftCell.Address = rng.Address The
Exit Su
End I
'End I
Nex

With ActiveSheet.OLEObjects.Add(
ClassType:="Forms.CheckBox.1",
Link:=False,
DisplayAsIcon:=False,
Left:=rng.Left + rng.Width / 2 - 10,
Top:=rng.Top,
Width:=rng.Width,
Height:=rng.Height
.Object.Caption = "
'.LinkedCell = rng.Addres
.Object.Value = Fals
End Wit

Application.ScreenUpdating = Tru
End I

End Su



"Melanie" wrote in message ..
I have created a macro to copy and paste certain rows in a worksheet. These rows contain check boxes. I would like the macro to also copy the check boxes
When I create the macro to copy and paste the rows, the check boxes are also copied. However, when I run the macro, the check boxes are not created(?).
Is this possible? Any ideas? Anyone? Anyone