Results 1 to 7 of 7
  1. #1
    3 Star Lounger
    Join Date
    Nov 2001
    Location
    Atlanta, Georgia, USA
    Posts
    274
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Replace characters (2000/XP)

    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

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Replace characters (2000/XP)

    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

  3. #3
    3 Star Lounger
    Join Date
    Nov 2001
    Location
    Atlanta, Georgia, USA
    Posts
    274
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Replace characters (2000/XP)

    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

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Replace characters (2000/XP)

    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

  5. #5
    3 Star Lounger
    Join Date
    Nov 2001
    Location
    Atlanta, Georgia, USA
    Posts
    274
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Replace characters (2000/XP)

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

  6. #6
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Replace characters (2000/XP)

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

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


    Steve

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Replace characters (2000/XP)

    Good idea.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •