adamsm
06-07-2010, 11:41 AM
Hi Anyone,
I'm currently using a standard module code to search and replace the text from all the sheets of the workbook.
I need help to modify the code so that I could do the job when its place in a user form module.
Current version of the code is placed in a standard module which prompts a message box to write the text that needed to be changed from all the worksheets of the workbook.
Here's what I have got so far,
Option Explicit
Sub ChgInfo()
Dim WS As Worksheet
Dim Search As String
Dim Replacement As String
Dim Prompt As String
Dim Title As String
Dim MatchCase As Boolean
Prompt = "Write the Initial Customer ID you want to replace?"
Title = "Change Customer ID"
Search = InputBox(Prompt, Title)
Prompt = "Write the replacement value?"
Title = "Replace Customer ID"
Replacement = InputBox(Prompt, Title)
For Each WS In Worksheets
WS.Cells.Replace What:=Search, Replacement:=Replacement, _
LookAt:=xlPart, MatchCase:=False
Next
End Sub
I have attached a sample workbook with a user form for better understanding of my problem.
Any help would be kindly appreciated.
Thanks in advance.
I'm currently using a standard module code to search and replace the text from all the sheets of the workbook.
I need help to modify the code so that I could do the job when its place in a user form module.
Current version of the code is placed in a standard module which prompts a message box to write the text that needed to be changed from all the worksheets of the workbook.
Here's what I have got so far,
Option Explicit
Sub ChgInfo()
Dim WS As Worksheet
Dim Search As String
Dim Replacement As String
Dim Prompt As String
Dim Title As String
Dim MatchCase As Boolean
Prompt = "Write the Initial Customer ID you want to replace?"
Title = "Change Customer ID"
Search = InputBox(Prompt, Title)
Prompt = "Write the replacement value?"
Title = "Replace Customer ID"
Replacement = InputBox(Prompt, Title)
For Each WS In Worksheets
WS.Cells.Replace What:=Search, Replacement:=Replacement, _
LookAt:=xlPart, MatchCase:=False
Next
End Sub
I have attached a sample workbook with a user form for better understanding of my problem.
Any help would be kindly appreciated.
Thanks in advance.