View Single Post
  #2   Report Post  
Posted to microsoft.public.excel.misc
Rick Rothstein \(MVP - VB\)[_10_] Rick Rothstein \(MVP - VB\)[_10_] is offline
external usenet poster
 
Posts: 1
Default Display feet inches and fraction

I would like to be able to input 11 3 1/2 and have the cell display
as 11'-3 1/2" Is this possible?


Right-click the sheet tab for the sheet you want this functionality on and
select View Code on the popup menu that appears. Copy/Paste the following
into the code window that opened up in the VBA editor...

Private Sub Worksheet_Change(ByVal Target As Range)
Dim X As Long
Dim TargetPattern As String
TargetPattern = Target.Value
For X = 1 To Len(Target.Value)
If InStr(" /", Mid$(TargetPattern, X, 1)) = 0 Then
Mid$(TargetPattern, X, 1) = "#"
End If
Next
On Error GoTo Whoops
Application.EnableEvents = False
If Target.Value Like TargetPattern Then
Target.Value = Replace(Target.Value, " ", "'-", , 1) & """"""
End If
Whoops:
Application.EnableEvents = True
End Sub

If you enter a number in the format you showed (with only single spaces
between the numbers), then it will be reformatted the way you want.

Rick