Thread: Replace Words in a Textbox

When you say textbox, do you mean a textbox? Or a cell?

Post a sample file with a textbox and a number of options you would want to replace and what to replace them with.

Hello djblois1,

The attached workbook contains a UserForm with a text box and command button. The substitution macro is run once the user has pressed the enter key. The command button closes the UserForm.

"Sheet2" has two dynamic named ranges: WordList and SubList. These hold the words you want to change and the substitution text. Add on to these as needed. The searches are not case sensitive, so upper and lower case characters are treated the same.

Here is the substitution macro code...

'Written: October 25, 2010
'Author:  Leith Ross (www.excelforum.com)

Private Sub TextBox1_AfterUpdate()

  Dim I As Long
  Dim RegExp As Object
  Dim SubList As Variant
  Dim WordList As Variant
  Dim X As String
  
    WordList = WorksheetFunction.Transpose(Range("WordList"))
    SubList = WorksheetFunction.Transpose(Range("SubList"))
    
    Set RegExp = CreateObject("VBScript.RegExp")
    RegExp.IgnoreCase = True
    
      For I = 1 To UBound(WordList)
        RegExp.IgnoreCase = True
        RegExp.Pattern = "(\b" & WordList(I) & "\b)(.*)"
        
        Do While RegExp.Test(TextBox1.Value)
          X = RegExp.Execute(TextBox1.Text)(0).SubMatches(0)
          TextBox1.Value = RegExp.Replace(TextBox1, SubList(I) & "$2")
         'Test to prevent endless loop
          If SubList(I) = X Then
             RegExp.IgnoreCase = False
          Else
             RegExp.IgnoreCase = True
          End If
        Loop
        
      Next I
      
    Set RegExp = Nothing
   
End Sub

Hello djblois1,

The text box in the workbook example I posted is on a VBA User Form. This is the same as a Control Toolbox (ActiveX) Text Box. Control Toolbox controls can be placed on either worksheets or VBA User Forms. You will need this type of control to make this work because it driven by the AfterUpdate event. This code can not be used with a Forms control, which can only be used on worksheets.