Change Name of ActiveX Command Button using VBA in Excel

Question

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

Siddharth Rout · Accepted Answer · 2012-05-17 19:36:15Z

8

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

enter image description here

edited May 17, 2012 at 19:36

answered May 17, 2012 at 19:04

Siddharth Rout

150k18 gold badges212 silver badges258 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Ehudz Over a year ago

Yes. The result is that the command button name stays as "CommandButton4".

Siddharth Rout Over a year ago

I just tried it. It changes the name of the newly created command button

Ehudz Over a year ago

Are you sure it changes both the NAME and Caption and not only the Caption? In VB editor the name field still says CommandButton4 after button creation

Ehudz Over a year ago

Such a simple fix. Why didn't I try that! Thanks again for your help! Much appreciated!

Collectives™ on Stack Overflow

Change Name of ActiveX Command Button using VBA in Excel

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related