How do I find and replace text in a Text Box?
--
Ed

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
DTHIS
www.nickhodge.co.uk


"Ed" wrote in message
...
How do I find and replace text in a Text Box?
--
Ed

Nick,

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


"Nick Hodge" wrote:

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
DTHIS
www.nickhodge.co.uk


"Ed" wrote in message
...
How do I find and replace text in a Text Box?
--
Ed

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
--
Gary's Student
gsnu200711

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, _
compa=vbTextCompare)
End With
Next TB
End With

End Sub



Ed wrote:

Nick,

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

"Nick Hodge" wrote:

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
DTHIS
www.nickhodge.co.uk


"Ed" wrote in message
...
How do I find and replace text in a Text Box?
--
Ed



--

Dave Peterson

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
--
Ed


"Gary''s Student" wrote:

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
--
Gary's Student
gsnu200711

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"

Ed wrote:

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
--
Ed

"Gary''s Student" wrote:

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
--
Gary's Student
gsnu200711


--

Dave Peterson

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Ed wrote:

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
--
Ed

"Gary''s Student" wrote:

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
--
Gary's Student
gsnu200711


--

Dave Peterson

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
--
Ed


"Dave Peterson" wrote:

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Ed wrote:

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
--
Ed

"Gary''s Student" wrote:

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
--
Gary's Student
gsnu200711


--

Dave Peterson

Hello,

Replace SubString with another string in TextBox.

Sub ReplaceTextBox(NameTextBox, SearchString, StringReplace)
ActiveSheet.Shapes(NameTextBox).TextFrame.Characte rs.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

On 24 mar, 16:00, Ed wrote:
How do I find and replace text in a Text Box?
--
Ed

Hi JB,

Thanks for answering my question.
--
Ed


"JB" wrote:

Hello,

Replace SubString with another string in TextBox.

Sub ReplaceTextBox(NameTextBox, SearchString, StringReplace)
ActiveSheet.Shapes(NameTextBox).TextFrame.Characte rs.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

On 24 mar, 16:00, Ed wrote:
How do I find and replace text in a Text Box?
--
Ed