Reference to Command Buttons Added During Runtime with VBA in Excel

Question

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

How is the user creating the command buttons? Manually or Via Code? — Siddharth Rout
– Siddharth Rout, Commented May 17, 2012 at 15:37
When the user clicks an "Add Node" button, an ActiveX button (which exists at start of runtime) will be copied and pasted to an appropriate location. I need to have access to these new buttons so I can change their behavior during runtime. I've also though about creating the buttons repeatedly with VBA, but not sure which is preferable. — Ehudz
– Ehudz, Commented May 17, 2012 at 15:40
Can you update the code above that you are using to copy and paste the existing button? Ah I just realized. Is it the same as it was in your previous question? — Siddharth Rout
– Siddharth Rout, Commented May 17, 2012 at 15:41
The 438 error is caused by With shp.OLEFormat.Object.Object instead of With shp.OLEFormat.Object — Roy Latham
– Roy Latham, Commented Nov 13, 2021 at 1:01

Siddharth Rout · Accepted Answer · 2012-05-17 17:56:14Z

4

Public Sub Node_Button_Duplication()
    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

    '~~> This will give you the name
    Debug.Print Selection.Name
End Sub

FOLLOWUP

If you know the name of the commandbutton then you can change the properties like this.

Option Explicit

Sub Sample()
    Dim shp As Shape

    '~~> Since you already have the name replace "CommandButton1" by
    '~~> the name that you have
    Set shp = ActiveSheet.Shapes("CommandButton1")

    With shp.OLEFormat.Object
        .Object.Caption = "Test"
        .Left = 15
        .Top = 15
    End With
End Sub

You can also combine the above two like this

Public Sub Node_Button_Duplication()
    Dim shp As Shape

    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

    '~~> This will give you the name
    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

And if you need to iterate through all the buttons then use this code.

Sub CommanButtons()
    Dim wks As Worksheet
    Dim OLEObj As OLEObject

    '~~> set it as per the relevant sheet
    Set wks = Worksheets("sheet1")

    For Each OLEObj In wks.OLEObjects
        If TypeOf OLEObj.Object Is MSForms.CommandButton Then
            Debug.Print OLEObj.Object.Caption
        End If
    Next OLEObj
End Sub

edited May 17, 2012 at 17:56

answered May 17, 2012 at 15:53

Siddharth Rout

150k18 gold badges212 silver badges258 bronze badges

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

5 Comments

Ehudz Over a year ago

I already know the name of the command button, but I would like to have access to it later on. For example, I will have 5 buttons, and I would like to access button 3, but don't know the VBA syntax. My guess is something like that given in my question. The button number is an integer variable. Essentially I would like to know how to concatenate the variable to "CommandButton" in something like Sheet1.CommandButton&i.Select

Siddharth Rout Over a year ago

What do you mean by Accessing it? Selecting it? Or Something like Application.Caller?

Ehudz Over a year ago

I would like to change the command button properties, such as caption, macro, deleting, etc.

Ehudz Over a year ago

The code is fine. In addition though, I would like to have something like ActiveSheet.Shapes("CommandButton"&Str(i)).Select where I concatenate an integer variable to the string "CommandButton" so I can access any button by the appropriate i variable value.

Ehudz Over a year ago

I have included the code which I used but gives me a runtime error

elmer · Accepted Answer · 2018-10-19 10:24:45Z

0

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

Sub x1()
    Me.OLEObjects("cmdOriginal").Copy
    Me.Paste
    With Me.OLEObjects(Me.OLEObjects.Count)
        .Name = "cmdButtonCopy"
        .Caption = "This is a copy"
    End With
End Sub

answered Oct 19, 2018 at 10:24

elmer

1

Collectives™ on Stack Overflow

Reference to Command Buttons Added During Runtime with VBA in Excel

2 Answers 2

5 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related