Change Name of ActiveX Command Button using VBA in Excel

command, excel, vba

Solution

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

Problem

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 ```

Original source