Cell value prefix

[NDB]

Hi,

I'm looking for a way to implement this ...

When a user selects cell A1 and enters a value (text), I would like the
displayed text to display a prefix also.

How can this be achieved?

eg. If user types in cell A1 the text value "Paul", when the enter key is
pressed, I would like the cell value displayed to be "England - Paul".
Likewise for example typing in "David" would give "England - David".

Cand this be done in using VBA or cell formatting?

TIA.

 

[Tom Ogilvy]

For VBA (you posted this in programming)
? ActiveCell.NumberFormat
"England - "@

- Manually
From the Menu
Format, cells, Number format Tab, choose custom

"England - "@

 

[Bernie Deitrick]

NDB,

You could use the custom format of

;;;"England - "@

But that would only affect how the value is displayed. But you could use an
event to change the actual value - this example (below) will work on column
A. Copy the code, right click on the cell tab and select "View Code" then
paste in the window that appears:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("A:A")) Is Nothing Then
Application.EnableEvents = False
If Left(Target.Value, 10) <> "England - " Then
Target.Value = "England - " & Target.Value
End If
Application.EnableEvents = True
End If
End Sub

HTH,
Bernie
MS Excel MVP

 

[NDB]

For VBA (you posted this in programming)
? ActiveCell.NumberFormat
"England - "@

- Manually
From the Menu
Format, cells, Number format Tab, choose custom

"England - "@

Excellent. Thanks Tom.

 

[NDB]

NDB,

You could use the custom format of

;;;"England - "@

But that would only affect how the value is displayed. But you could use an
event to change the actual value - this example (below) will work on column
A. Copy the code, right click on the cell tab and select "View Code" then
paste in the window that appears:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("A:A")) Is Nothing Then
Application.EnableEvents = False
If Left(Target.Value, 10) <> "England - " Then
Target.Value = "England - " & Target.Value
End If
Application.EnableEvents = True
End If
End Sub

HTH,
Bernie
MS Excel MVP

And thanks to you too Bernie.