Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
formatting for feet and inches
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
formatting for feet and inches
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
formatting for feet and inches
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/ |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
formatting for feet and inches
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/ . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
feet inches multiply with feet inches | About this forum | |||
convert decimal inches into feet and inches | Excel Discussion (Misc queries) | |||
Convert inches & display as Feet Inches and Fractions -- BUG FREE | Excel Worksheet Functions | |||
Custom formatting feet and inches from decimal | Excel Discussion (Misc queries) | |||
Converting from feet, inches and fractions to inches and decimal p | Setting up and Configuration of Excel |