vba 如何将 ActiveX 命令按钮复制到另一个工作表并保留按钮的名称
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/18507205/
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 command button to another sheet and keep the name of the button
提问by user1283776
I have tried the code below to copy an ActiveX command button from one sheet and paste it into another:
我已尝试使用下面的代码从一张纸上复制 ActiveX 命令按钮并将其粘贴到另一张纸中：
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.
但是我遇到的问题是它们将命令按钮从 hasACustomName 重命名为 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?
你能找出一种将命令按钮从一张纸粘贴到另一张纸上的替代方法，让我保留命令按钮的原始名称吗？
回答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
