0

I have tried the code below to copy an ActiveX command button from one sheet and paste it into another:

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

I've also tried:

Sheets("SRC").Shapes("hasACustomName").Copy 
Sheets("TRGT").Range("O1").Select 
Sheets("TRGT").Paste

But the problem I have with both of these is that they rename the Command button from hasACustomName to CommandButtonX.

Can you figure out an alternative method for pasting a command button from one sheet to another that let's me retain the original name of the Command button?

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