在 Excel 中使用 VBA 在运行时添加的命令按钮的参考

Reference to Command Buttons Added During Runtime with VBA in Excel

提问by Ehudz

During runtime, the user is able to add any number of ActiveX command buttons to Sheet 1. I need to have a reference to these new buttons with VBA, but am not sure how.

在运行时，用户可以将任意数量的 ActiveX 命令按钮添加到工作表 1。我需要使用 VBA 引用这些新按钮，但我不确定如何。

I know a logical progression which the button names will exhibit: ex.

我知道按钮名称将展示的逻辑进展：例如。

(Node#x2)-2=CommandButton#=i

(Node#x2)-2=CommandButton#=i

I need to somehow refer to these newly created buttons, I'm thinking is along the lines of this:

我需要以某种方式引用这些新创建的按钮，我认为是这样的：

Sheet1.Controls("CommandButton" & i).Select

If anyone knows the correct syntax or an alternate method please advise!

如果有人知道正确的语法或替代方法，请指教！

UPDATE

更新

Public Sub Node_Button_Duplication()
'
'Comments: Copies and pastes Node 1's button to the appropriate column

' Copy Node 1 button and paste in appropriate location
    ActiveSheet.Shapes("CommandButton1").Select
    Selection.Copy
    Cells(5, 10 + 7 * (NumNodes - 1) - 1).Select
    ActiveSheet.Paste
    Selection.ShapeRange.IncrementLeft 47.25
    Selection.ShapeRange.IncrementTop -13.5


End Sub

Follow-Up

跟进

Public Sub Node_Button_Duication()
'
'Comments: Copies and pastes Node 1's button to the appropriate column

Dim shp As Shape

' Copy Node 1 button and paste in appropriate location
    ActiveSheet.Shapes("CommandButton1").Select
    Selection.Copy
    Cells(5, 10 + 7 * (NumNodes - 1) - 1).Select
    ActiveSheet.Paste
    Selection.ShapeRange.IncrementLeft 47.25
    Selection.ShapeRange.IncrementTop -13.5

    Debug.Print Selection.Name

    Set shp = ActiveSheet.Shapes(Selection.Name)

    With shp.OLEFormat.Object.Object
        .Caption = "Test"
        .Left = 15
        .Top = 15
    End With

End Sub

This gives me a Run-time error "438: Object doesn't support this property or method. I don't particularly understand

这给了我一个运行时错误“438：对象不支持此属性或方法。我不是特别明白

shp.OLEFormat.Object.Object

回答by Siddharth Rout

Public Sub Node_Button_Duplication()
    ActiveSheet.Shapes("CommandButton1").Select
    Selection.Copy
    Cells(5, 10 + 7 * (NumNodes - 1) - 1).Select
    ActiveSheet.Paste
    Selection.ShapeRange.IncrementLeft 47.25
    Selection.ShapeRange.IncrementTop -13.5

    '~~> This will give you the name
    Debug.Print Selection.Name
End Sub

FOLLOWUP

跟进

If you know the name of the commandbutton then you can change the properties like this.

如果您知道命令按钮的名称，那么您可以像这样更改属性。

Option Explicit

Sub Sample()
    Dim shp As Shape

    '~~> Since you already have the name replace "CommandButton1" by
    '~~> the name that you have
    Set shp = ActiveSheet.Shapes("CommandButton1")

    With shp.OLEFormat.Object
        .Object.Caption = "Test"
        .Left = 15
        .Top = 15
    End With
End Sub

You can also combine the above two like this

您也可以像这样将上述两者结合起来

Public Sub Node_Button_Duplication()
    Dim shp As Shape

    ActiveSheet.Shapes("CommandButton1").Select
    Selection.Copy
    Cells(5, 10 + 7 * (NumNodes - 1) - 1).Select
    ActiveSheet.Paste
    Selection.ShapeRange.IncrementLeft 47.25
    Selection.ShapeRange.IncrementTop -13.5

    '~~> This will give you the name
    Debug.Print Selection.Name

    Set shp = ActiveSheet.Shapes(Selection.Name)

    With shp.OLEFormat.Object
        .Object.Caption = "Test"
        .Left = 15
        .Top = 15
    End With

End Sub

And if you need to iterate through all the buttons then use this code.

如果您需要遍历所有按钮，请使用此代码。

Sub CommanButtons()
    Dim wks As Worksheet
    Dim OLEObj As OLEObject

    '~~> set it as per the relevant sheet
    Set wks = Worksheets("sheet1")

    For Each OLEObj In wks.OLEObjects
        If TypeOf OLEObj.Object Is MSForms.CommandButton Then
            Debug.Print OLEObj.Object.Caption
        End If
    Next OLEObj
End Sub

回答by elmer

Suppose you have a command button (OLE object) with the name 'cmdOriginal' and you want to copy that button and paste it on the same workheet and change the name and caption of the new button into "cmdButtonCopy" and "This is a copy". The newly added button has the highest index in the OLEObjects collection! Place the following code in the code section of the worksheet

假设您有一个名为“cmdOriginal”的命令按钮（OLE 对象），并且您想复制该按钮并将其粘贴到同一个工作表上，并将新按钮的名称和标题更改为“cmdButtonCopy”和“This is a copy ”。新添加的按钮在 OLEObjects 集合中拥有最高的索引！将以下代码放在工作表的代码部分

Sub x1()
    Me.OLEObjects("cmdOriginal").Copy
    Me.Paste
    With Me.OLEObjects(Me.OLEObjects.Count)
        .Name = "cmdButtonCopy"
        .Caption = "This is a copy"
    End With
End Sub