Find & Replace in Text Box

N

Nick Hodge

Ed

We will need a little more detail than that

Data Validation textbox, activeX textbox, forms textbox, on a worksheet, on
a form,etc, etc.

In theory there is no search and replace for controls, you will need to
iterate the controls and test for their contents

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
[email protected]
www.nickhodge.co.uk
 
E

Ed

Nick,

Thanks for responding. I want to use the find and replace in the Text Box on
the drawing tool bar.
Ed
 
G

Gary''s Student

This will work for a "controls" type textbox:

Sub dural()
Dim s As String
s = ActiveSheet.TextBox1.Object.Value
s = Replace(s, "all", "a few")
ActiveSheet.TextBox1.Object.Value = s
End Sub
 
D

Dave Peterson

This will work in xl2k and above (replace was added in xl2k):

Option Explicit
Sub testme()
Dim FromStr As String
Dim ToStr As String
Dim TB As TextBox
Dim wks As Worksheet

FromStr = "test"
ToStr = "real"

Set wks = ActiveSheet

With wks
For Each TB In .TextBoxes
With TB
.Text = Replace(expression:=.Text, _
Find:=FromStr, _
Replace:=ToStr, _
Start:=1, _
Count:=-1, _
compare:=vbTextCompare)
End With
Next TB
End With

End Sub
 
E

Ed

Hi Dave and Gary"s Student,

Thanks for responding to my question. I am studying to become a Microsoft
Office Specialist. You two opened up a whole new area of Excel that I am not
yet familiar with. The more I learn about Excel, the more it amazes me. Can
one of you walk me through an example. For example, I have this text in
several texboxes: "30005SFN_I, 1-200". I would like to change it
to:"2611BFN_I, 1-200". How would I do that? The text box is from the Drawing
tool bar.

Thanks
 
D

Dave Peterson

If you're using xl2k or above and the code I suggested, then you'd change these
lines:

FromStr = "test"
ToStr = "real"

To what you want.

FromStr is short for "From String"
toStr is short for "To String"
 
E

Ed

Dave,

I just used the macro and it works great. Thanks for answering my question
and for opening up a whole new area of Excel to me.

Ed
 
J

JB

Hello,

Replace SubString with another string in TextBox.

Sub ReplaceTextBox(NameTextBox, SearchString, StringReplace)
ActiveSheet.Shapes(NameTextBox).TextFrame.Characters.Text = _
Replace(ActiveSheet.Shapes(NameTextBox).TextFrame.Characters.Text,
SearchString, StringReplace)
End Sub

Sub essai()
ReplaceTextBox "xxx", "sample", "zzz"
End Sub

http://cjoint.com/?dzr3sAgRjI

JB http://boisgontierj.free.fr
 
E

Ed

Hi JB,

Thanks for answering my question.
--
Ed


JB said:
Hello,

Replace SubString with another string in TextBox.

Sub ReplaceTextBox(NameTextBox, SearchString, StringReplace)
ActiveSheet.Shapes(NameTextBox).TextFrame.Characters.Text = _
Replace(ActiveSheet.Shapes(NameTextBox).TextFrame.Characters.Text,
SearchString, StringReplace)
End Sub

Sub essai()
ReplaceTextBox "xxx", "sample", "zzz"
End Sub

http://cjoint.com/?dzr3sAgRjI

JB http://boisgontierj.free.fr
Click to expand...
 
Top