Copy/Paste Controls in Excel 1

[sethdromgoole]

Hello,

I have an Excel worksheet the contains a check box and text. I highlight all the text, and the check box is included in this range. I copy the selection and paste it into a different worksheet, and only the text pastes. The controls do not paste unless I put the workbook into design mode first. I am building a macro to do this copy/paste action, so I do not want to manually select design mode every time I run the macro. Is there vba code that will put the workbook into design mode, and if not, does anyone have a suggestion on how to resolve my issue.

Thanks,

Seth Dromgoole

 

[SkipVought]

Seth Dromgoole,

    Dim shp As Shape, r1 As Range, r2 As Range
    Set r1 = Range("A1:A2")  'source range
    Set r2 = [E1]            'target paste range
    r1.Copy r2
    For Each shp In ActiveSheet.Shapes
        If Not Application.Intersect(shp.TopLeftCell, r1) Is Nothing Then
            With shp
                .Copy
                t = .TopLeftCell.Top - .Top
                l = .TopLeftCell.Left - .Left
            End With
            ActiveSheet.Paste
            With ActiveSheet.Shapes(ActiveSheet.Shapes.Count)
                .Top = r2.Top - t
                .Left = r2.Left - l
            End With
        End If
    Next

Skip,
[sub]
[red]Be advised:[/red] When you ignite a firecracker in a bowl of vanilla, chocolate & strawberry ice cream, you get...
Neopolitan Blownapart! [/sub]

 

[sethdromgoole]

Skip,

Thanks for the help so far. Another question. Now I am building a macro to clear the text and controls I just pasted to the new sheet. I select a range (Rows 11-40) and clear the text. Now, I want to delete all shapes that fall in this range. How do I determine if a shape falls in the range?

Thanks,

Seth

 

[SkipVought]

     Dim shp As Shape, r1 As Range, r2 As Range
    Set r1 = Range("A1:A2")  'source range
    Set r2 = [E1]            'target paste range
    r1.Copy r2
    For Each shp In ActiveSheet.Shapes
        If Not Application.Intersect(shp.TopLeftCell, r1) Is Nothing Then
            With shp
                .Copy
                t = .TopLeftCell.Top - .Top
                l = .TopLeftCell.Left - .Left
                ActiveSheet.Paste
                With ActiveSheet.Shapes(ActiveSheet.Shapes.Count)
                    .Top = r2.Top - t
                    .Left = r2.Left - l
                End With[b]
                .Delete[/b]
            End With
        End If
    Next

Skip,
[sub]
[red]Be advised:[/red] When you ignite a firecracker in a bowl of vanilla, chocolate & strawberry ice cream, you get...
Neopolitan Blownapart! [/sub]