Re: accept fractions from textbox

This would be my attempt at making it fully robust...

Function FracToDec(ByVal Fraction As String) As Double
Dim myC As Range
If Fraction Like "*\*#* *#*" Or Fraction Like "*/*/*" Or _
Fraction Like "*#* *#* *#*/*" Or Fraction Like "*[! /0-9-]*" Then
Err.Raise Number:=1001, Description:="Improperly formed fraction"
Exit Function
ElseIf Fraction Like "*#* *#*/*" Then
Fraction = Replace(Fraction, " ", Chr$(0), , 1)
End If
Fraction = Replace(Replace(Fraction, " ", ""), Chr$(0), " ")
FracToDec = Evaluate(Replace(Fraction, ",", ""))
End Function

Note that I implemented an "error number" (1001) that can be trapped for in code.

Rick


"Bernie Deitrick" <deitbe @ consumer dot org> wrote in message news:OBi8yObvIHA.3564@xxxxxxxxxxxxxxxxxxxxxxx
Gary''s Student,

Evaluate is an excellent idea - much better than my using a range object - but with a little extra code to clean things up, since evaluate doesn't like commas...

Bernie

Sub TestIt()
Dim myValue As Double
myValue = FracToDec(" 1,230 5 \ 6")
MsgBox myValue
End Sub

Function FracToDec(ByVal Fraction As String) As Double
Fraction = Application.Trim(Fraction)
Fraction = Replace(Fraction, "\", "/")
Fraction = Replace(Fraction, " /", "/")
Fraction = Replace(Fraction, "/ ", "/")
Fraction = Replace(Fraction, ",", "")
FracToDec = Evaluate(Fraction)
End Function

"Gary''s Student" <GarysStudent@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message news:4CFE0D1E-3B29-4A6D-AF13-E3BDB6C00134@xxxxxxxxxxxxxxxx
Make a Textbox (I used the Drawing Toolbar). Enter 3/4

Run the following:

Sub Macro1()
Dim s As String
Active***.Shapes("Text Box 1").Select
s = Selection.Characters.Text
MsgBox (Evaluate(s))
End Sub

Outputs .75
--
Gary''s Student - gsnu200788


"brianbanksia" wrote:

I cannot enter fractions in a TextBox and have them recognised as numbers.

If IsNumeric(UserForm1.TextBox2.Value) = False Then 'Quantity
MsgBox "Choose NUMERIC quantity. Transaction cancelled!", , "Company Name"
End If

I am having trouble getting this to work in Excel 2007; Vista whereas works
Excel 2003 XP.

Is there a better way or is it a machine setting problem rather than Excel
(eg Universal Date settings etc)
Thanks B



.