how to automatically modify captions of command buttons

[jqzhang]

I wrote a macro to search and change the captions of command buttons:

Private Sub fixcap()
If InStr(CommandButton1.Caption, "ABC") Then
CommandButton1.Caption = Application.Substitute(CommandButton1.Caption, "ABC", "ZZZ")
ElseIf InStr(CommandButton2.Caption, "AHY") Then
CommandButton2.Caption = Application.Substitute(CommandButton2.Caption, "ABC", "ZZZ")
....
End If
End Sub

If I have 20 buttons for each sheet and 50 sheets in total, keep writing the statements above will be awfully tedious. I tried to set up a For loop but it didn't work.
Could anyone help me out there?
Thanks in advance!
jqzhang

 

[SkipVought]

hi,

Sumthin like this?

Private Sub fixcap()
    Dim ws As Worksheet, shp As Shape, sReplace(1, 1) As String, i As Integer
    
    sReplace(0, 0) = "ABC"
    sReplace(0, 1) = "ZZZ"
    sReplace(1, 0) = "AHY"
    sReplace(1, 1) = "ZZZ"
    
    For Each ws In Worksheets
        For Each shp In ws.Shapes
            With shp
                If .Type = msoEmbeddedOLEObject Then
                    For i = 0 To UBound(sReplace)
                        If InStr(.OLEObject.Object.Object.Caption, sReplace(i, 0)) Then
                            CommandButton1.Caption = Application.Substitute(CommandButton1.Caption, sReplace(i, 0), sReplace(i, 1))
                        End If
                    Next
                End If
            End With
        Next
    Next
End Sub

Skip,
[sub]
[red]A palindrome gone wrong?[/red]
A man, a plan, a ROOT canal...
PULLEMALL![/sub]

 

[jqzhang]

Skip,
Thanks for the quick return.
But I got errors like 'Run-time error 438: object doesn't support this property or method'. The code is too much for me and no way for me to fix it.
Thanks again.
jqzhang