Macro to search through button texts and replace

**sm9748** · 08-21-2013, 06:18 AM

I have accidentally labelled a button (form control, rather than activeX) wrong and duplicated the sheet over 50 times within the same workbook.

I need a macro to search through all sheets and adjust the name. I have tried doing this using a few methods and to no avail.

Hope someone can help.

**sm9748** · 08-21-2013, 10:11 AM

I have managed to create a code to rename button1 but I need to search for any button which has text "ABC" on it within the entire workbook.
Anyone have any thoughts?

Sub change()
Dim btn As Shape
For Each btn In Workbook
 Sheet1.Shapes("Button 1").TextFrame.Characters.Text = "Click Me!"

End Sub

**Andy Pope** · 08-21-2013, 10:25 AM

Sub RecaptionButtons()
    Dim shtTemp As Worksheet
    Dim shpTemp As Shape
    
    For Each shtTemp In ActiveWorkbook.Worksheets
        For Each shpTemp In shtTemp.Shapes
            If shpTemp.Type = msoFormControl Then
                If shpTemp.OLEFormat.Object.Caption = "ABC" Then
                    shpTemp.OLEFormat.Object.Caption = "XYZ"
                End If
            End If
        Next
    Next
    
End Sub

just change "ABC" and "XYZ" to suit.

**sm9748** · 08-21-2013, 10:35 AM

Brilliant, thanks very much!

Macro to search through button texts and replace

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Macro to search through button texts and replace

Re: Macro to search through button texts and replace

Re: Macro to search through button texts and replace

Re: [SOLVED] Macro to search through button texts and replace

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Macro/s to create Search Bar to filter to search terms entered activated by Command Button

[SOLVED] How to Find and Replace a string if cell does not contain certain texts.

Search and Replace macro

Search and replace macro

[SOLVED] find for any of some search texts within a text

Bookmarks

Bookmarks

Posting Permissions