Change Customer ID

[adamsm]

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.

[adamsm]

Never mind if there's no help I'll use the code as it is.

[mdmackillop]

You really have it all, just a simple substitution.

Put all this in your Userform Code
[VBA]
Private Sub cmdChangeCustomerID_Click()
ChgInfo
End Sub
Private Sub ChgInfo()
Dim WS As Worksheet

For Each WS In Worksheets
WS.Cells.Replace What:=TextBox1, Replacement:=TextBox2, _
LookAt:=xlWhole, MatchCase:=False
Next
End Sub

[/VBA]

[adamsm]

Thanks a lot mdmackillop. Like you said I really had it all, all needed was just a simple substitution. Anyways, once again thanks for the help & I do really appreciate that.

Now I could change the text with the help of the user form

[mdmackillop]

You might want to put the Selection value into the Search textbox automatically on opening. Depends upon how it's used.

[adamsm]

How might I do this if I may ask? supposing I have a customer ID format as C 012345

[mdmackillop]

[VBA]Private Sub UserForm_Initialize()
TextBox1 = Selection
End Sub
[/VBA]

[adamsm]

Once again, thanks mdmackillop your modification has helped me a lot & I do really appreciate that. Now I can put the selection value into the Search text box automatically on opening of the user form.