在Excel中使用VBA更改ActiveX命令按钮的名称 [英] Change Name of ActiveX Command Button using VBA in Excel

问题描述

参考

我试图使用VBA更改ActiveX命令按钮的名称属性，具有以下代码：

 设置shp = ActiveSheet.Shapes（Selection.Name）
 
使用shp.OLEFormat.Object 
 .Object.Caption =Node& Str（NumNodes）
 .Name =Node& Str（NumNodes）
结束于
  

我可以更改字幕名称， name属性不能用上面的代码更改。我需要找到一种方法来连接一个字符串和一个int（NumNodes）的name属性。

UPDATE

这是复制命令按钮并将其粘贴到特定单元格位置的完整子例程。

 公共子Node_Button_Duplication（）
'
'注释：将节点1的按钮复制并粘贴到相应的列
 
 Dim shp As Shape 
 
'复制节点1按钮并粘贴到适当的位置
 
 ActiveSheet.Shapes（CommandButton1）。选择
 Selection.Copy 
单元格（5,10 + 7 *（NumNodes  -  1） -  1）.Select 
 ActiveSheet.Paste 
 Selection.ShapeRange.IncrementLeft 47.25 
 Selection.ShapeRange.IncrementTop -13.5 
 
 
设置shp = ActiveSheet.Shapes（Selection.Name）
 
 with shp.OLEFormat.Object 
 .Object.Caption =Node& Str（NumNodes）
 .Name =Node& Str（NumNodes）
以
结束
结束Sub 
  

解决方案

这是你想要的吗？

 设置shp = ActiveSheet.Shapes ）
 shp.Name =Node& Str（NumNodes）
 
使用shp.OLEFormat.Object 
 .Object.Caption =Node& Str（NumNodes）
以
结束 

FOLLOWUP >

只是尝试这个，它工作...

  Public Sub Node_Button_Duication 
 Dim shp As Shape 
 Dim NumNodes As Long 
 
 ActiveSheet.Shapes（CommandButton1）。选择
 Selection.Copy 
 Cells（5，10 + 7 *（NumNodes  -  1） -  1）。选择
 ActiveSheet.Paste 
 Selection.ShapeRange.IncrementLeft 47.25 
 Selection.ShapeRange.IncrementTop -13.5 
 
 NumNodes = 5 
 
设置shp = ActiveSheet.Shapes（Selection.Name）
 shp.Name =Node& Str（NumNodes）
 
使用shp.OLEFormat.Object 
 .Object.Caption =Node& Str（NumNodes）
结束于
结束子
  

更多关注

尝试此操作

 设置shp = ActiveSheet。形状（Selection.Name）
 
使用shp.OLEFormat.Object 
 .Object.Caption =Node& Str（NumNodes）
 .Name =Node& NumNodes 
结束于
  

注意，我更改了 Str 到 NumNodes ？

ActiveX控件名称不能有空格：）

立即尝试。

SNAPSHOT

>

Reference

I am trying to change the name property of ActiveX command buttons with VBA with the following code:

Set shp = ActiveSheet.Shapes(Selection.Name)

With shp.OLEFormat.Object
    .Object.Caption = "Node" & Str(NumNodes)
    .Name = "Node" & Str(NumNodes)
End With

I am able to change the caption name, but the name property cannot be changed with the above code. I need to find a way to concatenate a string with an int (NumNodes) for the name property.

UPDATE

This is the full subroutine which copies a command button and pastes it to a specific cell location. Properties, such as the name and caption are also changed upon button creation.

Public Sub Node_Button_Duplication()
'
'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


    Set shp = ActiveSheet.Shapes(Selection.Name)

    With shp.OLEFormat.Object
        .Object.Caption = "Node" & Str(NumNodes)
        .Name = "Node" & Str(NumNodes)
    End With

End Sub

解决方案

Is this what you are trying?

Set shp = ActiveSheet.Shapes(Selection.Name)
shp.Name = "Node" & Str(NumNodes)

With shp.OLEFormat.Object
    .Object.Caption = "Node" & Str(NumNodes)
End With

FOLLOWUP

Just tried this and it works...

Public Sub Node_Button_Duication()
    Dim shp As Shape
    Dim NumNodes As Long

    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

    NumNodes = 5

    Set shp = ActiveSheet.Shapes(Selection.Name)
    shp.Name = "Node" & Str(NumNodes)

    With shp.OLEFormat.Object
        .Object.Caption = "Node" & Str(NumNodes)
    End With
End Sub

MORE FOLLOWUP

Try this

    Set shp = ActiveSheet.Shapes(Selection.Name)

    With shp.OLEFormat.Object
        .Object.Caption = "Node" & Str(NumNodes)
        .Name = "Node" & NumNodes
    End With

Notice, I changed Str(NumNodes) to NumNodes?

The ActiveX Control Names cannot have spaces :)

Try now.

SNAPSHOT

这篇关于在Excel中使用VBA更改ActiveX命令按钮的名称的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！