How do I change a line or a word in several Text Boxes on a sheet and on
multiple sheets?

Thanks.


Ed

You didn't give us much clue as to what you have, what you want to change it
to, from where you want to initiate the change, etc., so we can only guess
as to how to solve your problem. My guess is to tell you to use the VBA
Replace function.

Rick


"Ed" wrote in message
...
How do I change a line or a word in several Text Boxes on a sheet and on
multiple sheets?

Thanks.


Ed

Following is one interpretation of what you asked for, but as you havn't
come back to Rick only a guess -

Sub test2()
' replace
ReplaceTBtext "Tom", "Dick"
End Sub

Sub ReplaceTBtext(sFind As String, sReplace As String)
Dim sOldText As String, sNewText As String
Dim ws As Worksheet
Dim tb As TextBox

For Each ws In ActiveWorkbook.Worksheets
For Each tb In ws.TextBoxes
sOldText = tb.Text
sNewText = Replace(sOldText, sFind, sReplace)
If sNewText < sOldText Then tb.Text = sNewText
Next
Next
End Sub

Regards,
Peter T

"Ed" wrote in message
...
How do I change a line or a word in several Text Boxes on a sheet and on
multiple sheets?

Thanks.


Ed

Hello Ed,

Peter's suggestion is correct. If you meant Shapes/TextBoxes by "Text Box"
in the question, we can use Peter's macro "ReplaceTBtext" to resolve the
problem.

If you have any other concern or need anything else, please feel free to
let us know.

Regards,
Jialiang Ge , remove 'online.')
Microsoft Online Community Support

==================================================
For MSDN subscribers whose posts are left unanswered, please check this
document: http://blogs.msdn.com/msdnts/pages/postingAlias.aspx

Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscripti...ult.aspx#notif
ications. If you are using Outlook Express/Windows Mail, please make sure
you clear the check box "Tools/Options/Read: Get 300 headers at a time" to
see your reply promptly.

Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscripti...t/default.aspx.
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.

Rick, Peter & Jialiang,

Thanks for responding to my question.

I have several text boxes on several work sheets that I have an address in.
The address gets changed whenever I start a new project which is every other
day just about. So I need a program that will change that address in every
text box.
--
Ed


"Jialiang Ge [MSFT]" wrote:

Hello Ed,

Peter's suggestion is correct. If you meant Shapes/TextBoxes by "Text Box"
in the question, we can use Peter's macro "ReplaceTBtext" to resolve the
problem.

If you have any other concern or need anything else, please feel free to
let us know.

Regards,
Jialiang Ge , remove 'online.')
Microsoft Online Community Support

==================================================
For MSDN subscribers whose posts are left unanswered, please check this
document: http://blogs.msdn.com/msdnts/pages/postingAlias.aspx

Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscripti...ult.aspx#notif
ications. If you are using Outlook Express/Windows Mail, please make sure
you clear the check box "Tools/Options/Read: Get 300 headers at a time" to
see your reply promptly.

Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscripti...t/default.aspx.
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.

Hello Ed,

To reset the Text Boxes' text (address), we can use the macro:

Sub SetTBtext(sAddress As String)
For Each ws In ActiveWorkbook.Worksheets
For Each tb In ws.TextBoxes
tb.Text = sAddress
Next
Next
End Sub

Then run the macro from a VB.NET program:
http://support.microsoft.com/kb/306682

or we can write a VB.NET automation client to run the code directly:
http://support.microsoft.com/kb/301982

Regards,
Jialiang Ge , remove 'online.')
Microsoft Online Community Support

=================================================
When responding to posts, please "Reply to Group" via your newsreader
so that others may learn and benefit from your issue.
=================================================
This posting is provided "AS IS" with no warranties, and confers no rights.

Hello Ed,

Would you mind letting me know the result of the suggestions? If you need
further assistance, feel free to let me know. I will be more than happy to
be of assistance.

Have a great day!

Regards,
Jialiang Ge , remove 'online.')
Microsoft Online Community Support

=================================================
When responding to posts, please "Reply to Group" via your newsreader
so that others may learn and benefit from your issue.
=================================================
This posting is provided "AS IS" with no warranties, and confers no rights.

Jialiang,

I used Peter T's macro and it worked great.

Since I am new to macros I did not quite understand what your macro is doing.

--
Ed


"Jialiang Ge [MSFT]" wrote:

Hello Ed,

Would you mind letting me know the result of the suggestions? If you need
further assistance, feel free to let me know. I will be more than happy to
be of assistance.

Have a great day!

Regards,
Jialiang Ge , remove 'online.')
Microsoft Online Community Support

=================================================
When responding to posts, please "Reply to Group" via your newsreader
so that others may learn and benefit from your issue.
=================================================
This posting is provided "AS IS" with no warranties, and confers no rights.