Replace characters (2000/XP)

Topic

New Reply

Hi,
I want to automatically replace a “space” with a “_” when the user enters data in a cell (name field).
If they enter John Smith, I want the entry to change to John_Smith.
I tried using the substitute function but got a circular reference message.
Thanks,
Scott

Reply | Quote

Replies

Say that you want to do this for cell A2.
– Right-click the sheet tab.
– Select View Code from the shortcut menu that pops up.
– Copy the following code into the module:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range(“A2”)) Is Nothing Then
Range(“A2”) = Replace(Range(“A2″), ” “, “_”)
End If
End Sub

Reply | Quote

Hans,
Thanks. That worked but I should have specified that I want that to happen for any value in A4 to A100
Can the code be modified for that range.
Thanks

Reply | Quote

Loop through the cells:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim oCell As Range
If Not Intersect(Target, Range(“A4:A100”)) Is Nothing Then
For Each oCell In Intersect(Target, Range(“A4:A100″)).Cells
oCell = Replace(oCell, ” “, “_”)
Next oCell
End If
End Sub

Reply | Quote

Hans,
Thanks again. Thats just what I needed.
Scott

Reply | Quote

I would recommend disabling the events to prevent recursive calls to the procedure

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim oCell As Range
    If Not Intersect(Target, Range("A4:A100")) Is Nothing Then
        Application.EnableEvents = False
        For Each oCell In Intersect(Target, Range("A4:A100")).Cells
            oCell = Replace(oCell, " ", "_")
        Next oCell
        Application.EnableEvents = True
    End If
End Sub

Steve

Reply | Quote

Good idea.

Reply | Quote