During runtime, the user is able to add any number of ActiveX command buttons to Sheet 1. I need to have a reference to these new buttons with VBA, but am not sure how.
I know a logical progression which the button names will exhibit: ex.
(Node#x2)-2=CommandButton#=i
I need to somehow refer to these newly created buttons, I'm thinking is along the lines of this:
Sheet1.Controls("CommandButton" & i).Select
If anyone knows the correct syntax or an alternate method please advise!
UPDATE
Public Sub Node_Button_Duplication()
'
'Comments: Copies and pastes Node 1's button to the appropriate column
' 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
End Sub
Follow-Up
Public Sub Node_Button_Duication()
'
'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
Debug.Print Selection.Name
Set shp = ActiveSheet.Shapes(Selection.Name)
With shp.OLEFormat.Object.Object
.Caption = "Test"
.Left = 15
.Top = 15
End With
End Sub
This gives me a Run-time error "438: Object doesn't support this property or method. I don't particularly understand
shp.OLEFormat.Object.Object
FOLLOWUP
If you know the name of the commandbutton then you can change the properties like this.
You can also combine the above two like this
And if you need to iterate through all the buttons then use this code.
Suppose you have a command button (OLE object) with the name 'cmdOriginal' and you want to copy that button and paste it on the same workheet and change the name and caption of the new button into "cmdButtonCopy" and "This is a copy". The newly added button has the highest index in the OLEObjects collection! Place the following code in the code section of the worksheet