Joseph, that worked but I would like to limit to cell(AC14)
and the other problem will be that must of the input will
be by Tabbing in and out of the cells, I don't expect them
to be using the Enter key that much.
Is there a way to write it so that it formats upon exit
from that cell?
I had to change the order of [newstring = Left(mystring,
(Position - 1)) & """" & Right(mystring, 2)& "'"]
It was coming up 5" 10'

Thanks

-----Original Message-----
Hi Tim,

I just wasn't happy with what I gave you so here's
something more
solid. In the VBE go to worksheet one and pick Selection
Change. Put
this code in:

Private Sub Worksheet_SelectionChange(ByVal Target As
Excel.Range)
Application.OnKey "~", "macro1"
Application.OnKey "{ENTER}", "macro1"
End Sub

Then create a macro called macro1 and Put this code into
it:

Sub Macro1()
mystring = ActiveCell.Value
If Len(mystring) < 1 Then End
If InStr(mystring, " ") < 1 Then End
Position = InStr(mystring, " ")
newstring = Left(mystring, (Position - 1)) & """" & Right
(mystring, 2)
& "'"
ActiveCell.Value = newstring
End Sub

Now go into your worksheet and put a value of
format "number space
number" into any cell and press either enter key.
I assume all a user is going to do when they enter the
value is either
press numeric enter or normal enter. Thats all this code
allows for
(haven't figured the click event yet). If anyone knows
how to improve
on this I would be most grateful!

Hope this is better for you Tim!


---
Message posted from http://www.ExcelForum.com/

.