参考在Excel中添加运行时使用VBA命令按钮 [英] Reference to Command Buttons Added During Runtime with VBA in Excel

问题描述

在运行时，用户可以任意数量的命令的ActiveX添加按钮说明书1.我需要用VBA这些新按钮的引用，但我不知道怎么样。

我知道哪个按钮名称将呈现逻辑进展：前

（节点＃2次）-2 =＃命令= I

我需要以某种方式将这些新创建的按钮，我想沿着这行：

  Sheet1.Controls（命令＆放大器; I）。选择
 

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

更新

 公用Sub Node_Button_Duplication（）

评语：复制和粘贴节点1的按钮，相应的列复制节点1按钮，并在适当的位置粘贴
    ActiveSheet.Shapes（CommandButton1的）。选择
    Selection.Copy
    细胞（5，10 + 7 *（NumNodes  -  1） -  1）。选择
    ActiveSheet.Paste
    Selection.ShapeRange.IncrementLeft 47.25
    Selection.ShapeRange.IncrementTop -13.5
结束小组
 

后续

 公用Sub Node_Button_Duication（）

评语：复制和粘贴节点1的按钮，相应的列昏暗的小水电为形状复制节点1按钮，并在适当的位置粘贴
    ActiveSheet.Shapes（CommandButton1的）。选择
    Selection.Copy
    细胞（5，10 + 7 *（NumNodes  -  1） -  1）。选择
    ActiveSheet.Paste
    Selection.ShapeRange.IncrementLeft 47.25
    Selection.ShapeRange.IncrementTop -13.5    Debug.Print Selection.Name    设置SHP = ActiveSheet.Shapes（Selection.Name）    随着shp.OLEFormat.Object.Object
        .Caption =测试
        。左= 15
        .TOP = 15
    结束与结束小组
 

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

  shp.OLEFormat.Object.Object
 

解决方案

 公用Sub Node_Button_Duplication（）
    ActiveSheet.Shapes（CommandButton1的）。选择
    Selection.Copy
    细胞（5，10 + 7 *（NumNodes  -  1） -  1）。选择
    ActiveSheet.Paste
    Selection.ShapeRange.IncrementLeft 47.25
    Selection.ShapeRange.IncrementTop -13.5    ~~＆GT;这会给你的名字
    Debug.Print Selection.Name
结束小组
 

后续

如果您知道命令的名称，然后您可以更改的属性是这样的。

 显式的选项子样品（）
    昏暗的小水电为形状    ~~＆GT;既然你已经有了名字被替换CommandButton1的
    ~~＆GT;你有名字
    设置SHP = ActiveSheet.Shapes（CommandButton1的）    随着shp.OLEFormat.Object
        .Object.Caption =测试
        。左= 15
        .TOP = 15
    结束与
结束小组
 

您还可以结合上述两个这样的

 公用Sub Node_Button_Duplication（）
    昏暗的小水电为形状    ActiveSheet.Shapes（CommandButton1的）。选择
    Selection.Copy
    细胞（5，10 + 7 *（NumNodes  -  1） -  1）。选择
    ActiveSheet.Paste
    Selection.ShapeRange.IncrementLeft 47.25
    Selection.ShapeRange.IncrementTop -13.5    ~~＆GT;这会给你的名字
    Debug.Print Selection.Name    设置SHP = ActiveSheet.Shapes（Selection.Name）    随着shp.OLEFormat.Object
        .Object.Caption =测试
        。左= 15
        .TOP = 15
    结束与结束小组
 

如果你需要通过所有的按钮来遍历然后用这个code。

 子CommanButtons（）
    昏暗的周作为工作表
    昏暗OLEOb​​j作为OLEOBJECT    ~~＆GT;其设置为按有关表
    设置周=工作表（工作表Sheet1）    对于每个OLEOb​​j在wks.OLEOb​​jects
        如果TypeOf运算OLEOb​​j.Object是MSForms.CommandButton然后
            Debug.Print OLEOb​​j.Object.Caption
        万一
    接下来OLEOb​​j
结束小组
 

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.

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

(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

shp.OLEFormat.Object.Object

解决方案

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

这篇关于参考在Excel中添加运行时使用VBA命令按钮的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！