Can you advise what is happening here please

[ipbr21054]

Morning,
I have a worksheet where in column A there are customers names.
There are no dupliacted names as when a repeat buyer is entered its then 002 003 etc etc
The names are like example.
Tom Jones
Tom Jones 001
Bob Sty 001
Charlie 001
Etc Etc

Recently something didnt work correctly as when i now start to type Tom Jones i see it change to Tom Jones 001 001 and at this point i havent left the cell yet if that helps.
I decided to remove the code so looking on the worksheet code i found & deleted the below.
I assume that was the correct code by looking at it ?

BUT
After saving,closing then opening the worksheet excel still wants to do something on its own.

Another example.
There is a customer called ALEX HOBBS 001 so a new row and in cell A6 i start to type ALEX H and now at this point i see the rest of the name entered like so OBBS 001 so when i leave the cell i see ALEX HOBBS 001 "so now there is a duplicate"

After deleting that code Ive looked on the worksheet and see nothing that mentions 001 or referene to A6 so i now think all the related code is gone.
I have also looked in THIS WORKBOOK etc but also nothing there that relates to it.

Why is excel doing this as it thinks i want to actually type ALEX HOBBS 001 again and is tying to be helpfull ?

If this helps if i type a name that isnt in column A the above doesnt happen, it seems to only do it if the name is allready in column A

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim Changed As Range, c As Range, DataRange As Range

  Set Changed = Intersect(Target, Columns("A"))
  If Not Changed Is Nothing Then
    Set DataRange = Range("A2", Range("A" & Rows.Count).End(xlUp))
    Application.EnableEvents = False
    For Each c In Changed
      If Len(c.Value) > 0 Then
          c.Value = c.Value & Format(Evaluate("countif(" & DataRange.Address & ",""" & c.Value & " 0*"")") + 1, " 000")
      End If
    Next c
    Application.EnableEvents = True
  End If
  If Not Intersect(Target, Range("A5:W5")) Is Nothing Then
    Application.EnableEvents = False
    Range("A5:W5").Value = UCase(Range("A5:W5").Value)
    Application.EnableEvents = True
End If
End Sub

 

[RoryA]

That sounds like the 'Enable autocomplete for cell values' setting in the Advanced section of Excel Options.

 

[ipbr21054]

Hi,
Now that you pointed me in the right place you are correct thansk.