I would like to format a cell automattically add the ' " to
a person height if the the data is only put in as 5 10.
Is there a way to format a cell for this.

Hi Tim,

I found a way of doing it from a command button:

Private Sub CommandButton1_Click()
Dim feet, inches

feet = InputBox("Feet?")
inches = InputBox("Inches?")
Range("A1").Value = feet & "'" & " " & inches & "''"

End Sub

Hope that helps

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

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/

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/

.