PDA

View Full Version : Change Customer ID



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.

adamsm
06-07-2010, 12:18 PM
Never mind if there's no help I'll use the code as it is.

mdmackillop
06-07-2010, 12:27 PM
You really have it all, just a simple substitution.

Put all this in your Userform Code

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

adamsm
06-07-2010, 12:35 PM
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
06-07-2010, 12:45 PM
You might want to put the Selection value into the Search textbox automatically on opening. Depends upon how it's used.

adamsm
06-07-2010, 01:15 PM
How might I do this if I may ask? supposing I have a customer ID format as C 012345

mdmackillop
06-07-2010, 01:17 PM
Private Sub UserForm_Initialize()
TextBox1 = Selection
End Sub

adamsm
06-07-2010, 01:24 PM
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.