Changing the name of an ActiveX command button using VBA in Excel

Question

Reference

I am trying to change the ActiveX command button name property using 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 can change the name of the header, but the name property cannot be changed using the above code. I need to find a way to concatenate a string with int (NumNodes) for the name property.

UPDATE

This is a complete routine that copies a command button and pastes it into a specific cell. Properties, such as name and caption, also change when the button is created.

 Public Sub Node_Button_Duplication() ' 'Comments: Copies and pastes Node 1 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 

Answer 1

Is that 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 

LEARN MORE

try it

  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 ?

ActiveX control names cannot have spaces :)

Try now.

Snapshot