excel - 在 Excel 中使用 VBA 对运行时添加的命令按钮的引用

Question

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

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

(节点#x2)-2=命令按钮#=i

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

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

如果有人知道正确的语法或替代方法，请告知!

更新

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

跟进

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

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

shp.OLEFormat.Object.Object

Answer 1

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

跟进

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

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

你也可以像这样结合上面两个

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

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

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