formatting for feet and inches

  • Thread starter Thread starter Tim
  • Start date Start date
T

Tim

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
 
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!
 
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
 
Back
Top