Command Button Collection?

patrickmuldoon99

Active Member
Joined
Jun 27, 2006
Messages
345
Is it possible to iterate through a collection of command buttons in my workbook, changing the caption on them?

e.g.

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

cheers guys
 

Some videos you may like

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.

Jaafar Tribak

Well-known Member
Joined
Dec 5, 2002
Messages
8,028
Office Version
  1. 2016
Platform
  1. Windows
If you are using worksheet embeeded Buttons created from the Controls Toolbar use this :

Code:
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 :

Code:
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,114,407
Messages
5,547,774
Members
410,811
Latest member
adustin42
Top