vba 如何将 ActiveX 控件复制到另一个工作表以防止控件名称更改
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/18505170/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to copy an ActiveX control over to another sheet preventing the name change of the control
提问by user1283776
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 HasACustomNameto CommandButton1.
当我粘贴它时，它被重命名HasACustomName为CommandButton1。
Can I either copy/paste it in a way that retains the name or change the name after pasting?
我可以以保留名称的方式复制/粘贴它或在粘贴后更改名称吗？
回答by
ActiveX
ActiveX
You can copy an ActiveX Control from one sheet to another with the below code.
您可以使用以下代码将 ActiveX 控件从一张纸复制到另一张纸。
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.
要将按钮从一张纸复制到另一张纸以防止自动更改名称，请使用以下代码。默认情况下，Excel 为复制的按钮（即使在不同的工作表上）提供一个新名称，因此您必须重命名它以匹配您正在复制的按钮的名称。
Use CopyButton()sub to achieve it. There are 4 required parameters
使用CopyButton()sub 来实现它。有4个必需的参数
from- sheet name to copy the button frombtnName- the name of the control you want to copytoWorksheet- target worksheetrng- target range to associate with the button
from- 要从中复制按钮的工作表名称btnName- 要复制的控件的名称toWorksheet- 目标工作表rng- 与按钮关联的目标范围
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
