3

I am using the code below to copy a command button from one sheet and paste it into another:

Sheets("SRC").HasACustomName.Copy
Sheets("TRGT").Range("O1").PasteSpecial

When I paste it, it get's renamed from HasACustomName to CommandButton1.

Can I either copy/paste it in a way that retains the name or change the name after pasting?

Community
  • 1
  • 1
user1283776
  • 19,640
  • 49
  • 136
  • 276

1 Answers1

4

ActiveX

You can copy an ActiveX Control from one sheet to another with the below code.

Note: you cannot have two objects of the same name on one spreadsheet.

Sub CopyActiveX()
    Application.ScreenUpdating = False
    Dim x As OLEObject, y As OLEObject
    Set x = Sheets("SRC").OLEObjects("HasCustomName")
    Set y = x.Duplicate
    Dim xName As String
    xName = x.Name
    y.Cut
    With Sheets("TRGT")
        .Paste
        .OLEObjects(.OLEObjects.Count).Name = xName
        .Activate
    End With
    Application.ScreenUpdating = True
End Sub

Form Control

To copy a button from one sheet to another preventing the automatic name change use the below code. Excel by default gives a new name to a copied button (even on a different sheet) so you have to rename it to match the name of the button youre copying.

Use CopyButton() sub to achieve it. There are 4 required parameters

  • from - sheet name to copy the button from
  • btnName - the name of the control you want to copy
  • toWorksheet - target worksheet
  • rng - target range to associate with the button
Sub CopyPasteButton()
    CopyButton "SRC", "Button 1", "TRGT", "B10"
End Sub

Private Sub CopyButton(from As String, btnName As String, toWorksheet As String, rng As String)
    Application.ScreenUpdating = False
    Sheets(from).Shapes(btnName).Copy
    Sheets(toWorksheet).Activate
    Sheets(toWorksheet).range(rng).Select
    Sheets(toWorksheet).Paste
    Selection.ShapeRange.Name = btnName
    Application.ScreenUpdating = True
End Sub
  • Thanks! I've changed to: Sheets("SRC").Shapes("hasACustomName").Copy Sheets("TRGT").Range("O1").Select Sheets("TRGT").Paste – user1283776 Aug 29 '13 at 08:37
  • Nope, after testing I see that this also changes the name of the CommandButton. The caption is unchanged with both the original solution and your solution. But the name changes with both solutions – user1283776 Aug 29 '13 at 08:42
  • @user1283776 ok, I have updated my answer. It copies the button and renames it after. I have debug.printed the name before and after to confirm the are the same –  Aug 29 '13 at 09:02
  • No downvote mehow! I just returned it to neutral. Wasy that inappropriate? Selection.ShapeRange.Name = btnName generates an error message "Object doesn't support this property or method". – user1283776 Aug 29 '13 at 09:11
  • what excel version? is it a form control or activex? and you sure haven't modified the code? how many buttons you have on the spreadsheet? Please see [this](http://msdn.microsoft.com/en-us/library/office/aa223091(v=office.11).aspx) for reference. I have checked the code it does not fail for me. –  Aug 29 '13 at 09:15
  • My button is an ActiveXControl. You method seems to work for form buttons, but not for ActiveXbuttons. – user1283776 Aug 29 '13 at 09:28
  • I see I wrote form control in the title. Sorry about the confusion and thanks for the help! – user1283776 Aug 29 '13 at 09:32
  • about time I realize that its an ActiveX control. There you go I updated the answer for you –  Aug 29 '13 at 09:44
  • 1
    Thank you very much. But I still can't get it to work. It creates a new button and copies it to a new sheet, but the button loses its name and is renamed into CommandButtonX. – user1283776 Aug 29 '13 at 09:53
  • @user1283776 ok try one more time with the new code in the answer –  Aug 29 '13 at 09:58