如何将ActiveX控件复制到另一张表，防止控件的名称更改 [英] How to copy an ActiveX control over to another sheet preventing the name change of the control

问题描述

我正在使用下面的代码从一个工作表中复制一个命令按钮并将其粘贴到另一个工作表中：

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

当我粘贴它，它从 HasACustomName 重命名为 CommandButton1 。

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?

推荐答案

ActiveX

您可以使用以下代码将ActiveX控件从一张工作表复制到另一张。

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

---

要将按钮从一张纸复制到另一张，禁止自动更名使用以下代码。默认情况下，Excel会为复制的按钮（即使在不同的工作表）上添加一个新名称，因此您必须重命名它，以匹配您复制的按钮的名称。

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.

使用 CopyButton（） sub来实现它。有4个必需参数

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

• 从 - 将名称从


• btnName - 要复制的控件的名称


• toWorksheet - 目标工作表


• rng - 与按钮相关联的目标范围

• 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

这篇关于如何将ActiveX控件复制到另一张表，防止控件的名称更改的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！