Command Button Collection?

[patrickmuldoon99]

Is it possible to iterate through a collection of command buttons in my workbook, changing the caption on them?

e.g.

for each button in commandbuttons
commandbutton.text = "mychangedtext"
next button

cheers guys

 

[Jaafar Tribak]

If you are using worksheet embeeded Buttons created from the Controls Toolbar use this :

Sub ChangeCaptionOf_ButtonsFrom_ControlsToolBar_TEST(Sht As Worksheet)

    Dim oCbt  As OLEObject
    
    For Each oCbt In Sheet1.OLEObjects
    
    If oCbt.progID = "Forms.CommandButton.1" Then
            oCbt.Object.Caption = "MyChangedText"
        End If
    Next

End Sub

Sub Example1()

    ChangeCaptionOf_ButtonsFrom_ControlsToolBar_TEST Sheet1

End Sub

For Buttons created from the Forms ToolBar use this :

Sub ChangeCaptionOf_ButtonsFrom_FormsToolBar_TEST(Sht As Worksheet)

    Dim oButton  As Shape
    
    For Each oButton In Sheet1.Shapes
    
    If TypeName(oButton.OLEFormat.Object) = "Button" Then
        oButton.OLEFormat.Object.Caption = "MyChangedText"
    End If
    
    Next

End Sub

Sub Example2()

    ChangeCaptionOf_ButtonsFrom_FormsToolBar_TEST Sheet1

End Sub

Regards.

 

[patrickmuldoon99]

Excellent Jaafar, just what I was after!

Cheers mate.