Prefix by VBA

[ElNaheep]

I need an Excel VBA code to add a prefix in each cell of column A when ever i enter a text to Column A.

Thank you

 

[VoG]

Right click the sheet tab, select View Code and paste in

Private Sub Worksheet_Change(ByVal Target As Range)
Const prefix As String = "xyz"
If Target.Column = 1 Then
    Application.EnableEvents = False
    Target.Value = prefix & Target.Value
    Application.EnableEvents = True
End If
End Sub

Change the prefix to suit.

 

[Rick Rothstein]

I need an Excel VBA code to add a prefix in each cell of column A when ever i enter a text to Column A.

Does the value in the cell have to physically change so that its value permanently has the prefix added to it or would simply formatting the cell so it just looked like the prefix was added be enough (with the actual value in the cell remaining exactly as entere)?

 

[ElNaheep]

Thanks for fast reply

But the problem it add this prefix each time i activate the cursor in the cell!!
how to fix it? so it only add the prefix once in a cell and if i decideded to delete it from a cell it don't add it again to that cell ?

Thank you

 

[VoG]

Try

Private Sub Worksheet_Change(ByVal Target As Range)
Const prefix As String = "xyz"
If Target.Column = 1 Then
    Application.EnableEvents = False
    If Target.Value <> "" Then Target.Value = prefix & Target.Value
    Application.EnableEvents = True
End If
End Sub

This will only activate when you change a cell, not when you select it.

 

[ElNaheep]

I want it to add the prefix once to the cell and if decided to make a change to the cell, it will not add the prefix again

 

[VoG]

It would have helped if you had posted your complete question in the first place and not a simplification. Maybe

Private Sub Worksheet_Change(ByVal Target As Range)
Const prefix As String = "xyz"
If Target.Column = 1 Then
    Application.EnableEvents = False
    If Target.Value <> "" And Left(Target, Len(prefix)) <> prefix Then Target.Value = prefix & Target.Value
    Application.EnableEvents = True
End If
End Sub

but I think that Rick's idea is the best.

 

[ElNaheep]

I'm soo sorry to bother you sir, and you are right I've should explain what i want from the start.

And now i want another thing, Can i make excel notify me immediately for duplication which occur in column A ?

thank you