Macro to Find and Replace Inside Specific Text Boxes

[pschappert]

Hi,

I'm looking for a way to replace certain words inside a text box based on cell values in a different worksheet. I am not sure if a 'Find and Replace' macro is the best way to do this, but here's an example of what I'm looking for:

(Inside text box on Worksheet 2)

"The quick brown fox jumped over the lazy dog."

(Inside cells A1 on Worksheet 1)

"happy"


My question is, how can I get "happy" to replace "lazy"?

*Note: there are several text boxes that I need to do this in. However, the value in the cell ("happy" in my example) will always be the same. The biggest problem I am facing is the stupid 256-character limit that Excel has placed on formulas inside text boxes.

I appreciate all the help I can get in advance!

Thanks,

Patrick

 

[circledchicken]

Hi Patrick,

You can try:

Sub example()

Dim ws As Worksheet
Dim tBox As Shape
Dim stBox As String

Set ws = Sheets("Sheet2")
For Each tBox In ws.Shapes
    stBox = tBox.TextFrame.Characters.Text
    tBox.TextFrame.Characters.Text = Replace(stBox, "lazy", Sheets("Sheet1").Range("A1"))
Next

End Sub

 

[pschappert]

Sorry, but it still won't work. I'm getting some funky error message about it not being supported. Any ideas?

 

[circledchicken]

What's the error message and on what line?

 

[pschappert]

"Run-Time Error 438:

Object doesn't support this method"



Line 7 of code ("stBox = tBox.TextFrame.Characters.Text"


I think it's simply saying that text boxes don't allow what this macro is doing.

Sorry to be a pain!

Thanks again

 

[circledchicken]

What version of Excel are you using - seems to work for me on Excel 2010. I have no idea if this would help but maybe try replacing:

Dim tBox As Shape

with

Dim tBox As Object

 

[pschappert]

No dice...I'm running 2007. I dont understand why it would make a difference, but hey, what do I know? I wonder if I made an error in my substitution...

 

[circledchicken]

Maybe its because you also have shapes other than text boxes on your worksheet? Try:

Sub example()

Dim ws As Worksheet
Dim tBox As Object
Dim stBox As String

Set ws = Sheets("Sheet2")
For Each tBox In ws.Shapes
    On Error Resume Next
    stBox = tBox.TextFrame.Characters.Text
    tBox.TextFrame.Characters.Text = Replace(stBox, "lazy", Sheets("Sheet1").Range("A1"))
    On Error GoTo 0
Next

End Sub

 

[pschappert]

Works like a charm! Thanks so much! Any way it can easily be edited with an OR statement...for example, rather than replace "lazy," i want to replace "lazy" OR "sloppy"? Cheers!

 

[circledchicken]

Your welcome!
Try:

Sub example()

Dim ws As Worksheet
Dim tBox As Object
Dim stBox As String
Dim vArr As Variant
Dim s

Set ws = Sheets("Sheet2")
vArr = Array("lazy", "sloppy") 'you can add more words to replace here 
For Each tBox In ws.Shapes
    On Error Resume Next
    stBox = tBox.TextFrame.Characters.Text
    For Each s In vArr
        stBox = Replace(stBox, s, Sheets("Sheet1").Range("A1"))
    Next s
    tBox.TextFrame.Characters.Text = stBox
    On Error GoTo 0
Next

End Sub