Copy/Paste with assigned macro

[i_stoeva]

Hi everybody,

Could someone tell me how to copy a toggle button with the assigned macro to it on other workbook?

Thanks,
Iva

 

[Andrew Poulsom]

If you copy the entire sheet, the code will also be copied.

It's easy to copy a control:

Sub Test()
    Dim WB1 As Workbook
    Dim WB2 As Workbook
    Dim Sh1 As Worksheet
    Dim Sh2 As Worksheet
    Set WB1 = Workbooks("Book1.xls")
    Set WB2 = Workbooks("Book2.xls")
    Set Sh1 = WB1.Worksheets("Sheet1")
    Set Sh2 = WB2.Worksheets("Sheet1")
    With Sh1.OLEObjects("ToggleButton1")
        .Copy
        Sh2.Paste Sh2.Range(.TopLeftCell.Address)
    End With
End Sub

but adding the code will involve using the Microsoft Visual Basic For Applications Extensibility library:

http://www.cpearson.com/excel/vbe.htm