formatting cells

[shacho]

I want to be able to enter text in to any cell and automatically have my name as a prefex added on it, for example, if I enter the word "test" into a cell I would like it to look like to read
"tom-test" after I press enter, or if I enterd the word "blue" I would like it to read "tom-blue" after I press enter. Could someone tell me how to do this in Excel.

Thanx

 

[Anne Troy]

I think I already answered this for you at another site.

 

[Anne Troy]

Pasted:

Requires this code:

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
If Not Application.Intersect(Target, Range("A1:A10")) Is Nothing Then
Target(1).Value = "Tom-" & Target(1).Value
End If
Application.EnableEvents = True
End Sub


Change the range to that desired. Hit Alt-F11 and paste it into the code window for the desired sheet.

 

[shacho]

I dont know where to enter this text

 

[Anne Troy]

Just copy from *sub* to *end sub* in my post above.

Open your Excel file.
Hit Alt-F11 to bring up the Visual Basic Editor (VBE).
On the top-left, double-click the sheet that you intend to have this trick work.
Make sure you see the code window at the right.
Click inside the code window and paste.
Change the range A1:A10 in my code to be the range that you want the code to work on, i.e., A:C to have it work on all columns A through C.

Close the VBE window.
Type something into the area that you have named above (A:C in my example) to test the code. If it works, save your file.

 

[shacho]

That worked really good, thank you very much. There is one little thing more that I would like to do just to tweak this spead sheet a bit. Once I go into that cell what do I do if I want to delete it. The prefix still remains.

 

[Anne Troy]

That's a tough one that I cannot answer. Post in your question here before it drops to the bottom of the 2nd page each day to see if anyone else can help.

Sorry shacho.

 

[Escalus]

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
If Not Application.Intersect(Target, Range("A1:A10")) Is Nothing Then
If Target.Value <> "" Then Target(1).Value = "Tom-" & Target(1).Value
End If
Application.EnableEvents = True
End Sub

 

[Anne Troy]

A VERY WELCOME to the board, Escalus. That's terrific!