none
Insert Text on Worksheet Change Event RRS feed

  • Question

  • Hello -

    I am looking for some assistance with the below. I am attempting to automatically add "@nationalcore.org" to the end of text that was entered into a particular cell. This should happen automatically when the user tabs/exits out of the cell.

    Example; user enters "test" into cell E15. When the user tabs away from (change event) cell E15, the cell should update to "test@nationalcore.org"

    Here is what I have come up with so far (below). This works, somewhat...however, it adds the "@nationalcore.org" part many many times after the "test" part? Not sure why or how to resolve?

    Private Sub Worksheet_Change(ByVal Target As Range)

        Application.ScreenUpdating = False
            
        If Target.Address = "$E$15" And _
             Target.Value <> "" Then
                Target.Value = Target.Value & "@nationalcore.org"
            Exit Sub
        End If
        
    End Sub

    And here is the result :-( ....

    Lol...am I close? Maybe a better way of doing this? Any assistance or suggestions will be greatly appreciated.

    Thanks in advance for your time!

    Tuesday, June 25, 2019 8:23 PM

Answers

  • You have to disable event handling temporarily to prevent Worksheet_Change from calling itself over and over again:

    Private Sub Worksheet_Change(ByVal Target As Range)
        Application.ScreenUpdating = False
        Application.EnableEvents = False
        If Target.Address = "$E$15" And _
             Target.Value <> "" Then
                Target.Value = Target.Value & "@nationalcore.org"
        End If
        Application.EnableEvents = True
        Application.ScreenUpdating = True
    End Sub


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Marked as answer by rstreets2 Wednesday, June 26, 2019 6:39 PM
    Tuesday, June 25, 2019 8:28 PM

All replies

  • You have to disable event handling temporarily to prevent Worksheet_Change from calling itself over and over again:

    Private Sub Worksheet_Change(ByVal Target As Range)
        Application.ScreenUpdating = False
        Application.EnableEvents = False
        If Target.Address = "$E$15" And _
             Target.Value <> "" Then
                Target.Value = Target.Value & "@nationalcore.org"
        End If
        Application.EnableEvents = True
        Application.ScreenUpdating = True
    End Sub


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Marked as answer by rstreets2 Wednesday, June 26, 2019 6:39 PM
    Tuesday, June 25, 2019 8:28 PM
  • Thank you Hans! Once again you have saved the day! Worked perfectly. Thank you so much for your assistance!
    Wednesday, June 26, 2019 6:39 PM