Change Name of ActiveX Command Button using VBA in Excel
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
Solution 1:
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