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

 

[pschappert]

Ok Ok!!! Last time, I promise! As I play around, I continually discover that the original thing I asked for is not at all what I am looking for. Any way you can make it apply to single text boxes (one at a time)? I want it combined into one macro, but to have it apply to different text boxes individually. Any ideas?

Thank you so much! If there's ever any way I can help you, please don't hesitate to ask!!

Patrick

 

[circledchicken]

No worries, your welcome!

As I play around, I continually discover that the original thing I asked for is not at all what I am looking for.

Haha! - thats no good!, but yeah you always find ways to improve these things.

I'm not entirely sure I understand what you want. Could you post some examples showing before and after you run the macro?

 

[pschappert]

Ok, so here goes...

(Sheet 2)

(Text Box 1)
Replace "brown" with "Sheet1!$F$48"

(Text Box 2)
Replace "yellow" with "Sheet1!$F$12"

etc...

Basically, rather than apply the macro to every single text box on Sheet2, can I make it apply to only select text boxes...?

Thanks

 

[circledchicken]

One way you might set this up is have three columns in Sheet1 - for example:

• A1: "TextBox 1" (without the quotes, enter the exact name of the Text Box in cell A1 - the name in the rectangle on the top left when you select a text box)
• B1: "brown" (without the quotes, the word you want replaced)
• C1: "Sheet1!F48" (without the quotes, the cell reference, including Sheet name of the replacement word)
• A2: "TextBox 2"
• B2: "yellow"
• C2: "Sheet1!F12"

etc...
Then use the following to cycle through the text boxes and make the replacements for individual textboxes:

Sub example()

Dim ws As Worksheet
Dim vArr As Variant
Dim i As Long
Dim stBox As String

Set ws = Sheets("Sheet2")
vArr = Sheets("Sheet1").Range("A1:C2") 'change to your range which contains the TextBox names, words and replacement word cell references
For i = 1 To UBound(vArr, 1)
    stBox = ws.Shapes(vArr(i, 1)).TextFrame.Characters.Text
    stBox = Replace(stBox, vArr(i, 2), Range(vArr(i, 3)))
    ws.Shapes(vArr(i, 1)).TextFrame.Characters.Text = stBox
Next i

End Sub

 

[pschappert]

Something isn't right :/ I made all the necessary replacements, but there seem to be some problems. If I step into the Macro, and hover over some of the coding, it shows "Type Mismatch<TYPE Mismatch><TYPE Mismatch><TYPE Mismatch>" Is this a problem? Sorry...as you can probably tell, I'm not at all familiar with VBA & Macros :P

 

[pschappert]

It also says "Method Range of Object_Global_Failed"

 

[circledchicken]

What line is the error on? Are the names of the Text Boxes exactly the same as the names in column A? If your using a different range to "A1:C2" on Sheet1 - have you amended the code to reflect that?

 

[pschappert]

Any way to replace entire text boxes?

For example, if cell C60 = TRUE, insert TextBox 81, but if C60 = FALSE, insert TextBox 54

 

[pschappert]

Still looking for help on this one!

I am looking for a macro to browse and insert a picture in a text box on a different worksheet.

I want to press a button and have it pull up access to my computer, so I can insert a picture. When I click "Insert," I want it to insert it into the text box on the separate worksheet.