VBA won't format into fraction

D

damorrison

I have a formula in VBA that is calculated and displayed in a Label,
and then inserted into a cell,

I cannot get the darned thing to display as a Fraction or get it
formatted into the cell as a fraction
Here is the formula

Private Sub ListBox2_Change()
If ListBox2.Value = "H.W.R." Then
Label17.Caption = TextBox4.Text - 1 / 8
Else
Label17.Caption = TextBox4.Text + 1 / 8
End If

End Sub

Any Ideas
 
B

Bob Phillips

Here is one, incredibly convoluted way

Dim saveValue
Dim saveFormat

With Range("A1")
saveValue = .Value
saveFormat = .NumberFormat
.Value = TextBox4.Text - 1 / 8
.NumberFormat = "#??/??"
If ListBox2.Value = "H.W.R." Then
Label17.Caption = Format(TextBox4.Text - 1 / 8, "#??/??")
Else
Label17.Caption = TextBox4.Text + 1 / 8
End If
.Value = saveValue
.NumberFormat = saveFormat
End With


--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)
 
D

Dave Peterson

Maybe something like:

Label17.Caption = Application.Text(cdbl(TextBox4.Text) - 1 / 8, "??/??")
 
P

Paul Lautman

damorrison said:
I have a formula in VBA that is calculated and displayed in a Label,
and then inserted into a cell,

I cannot get the darned thing to display as a Fraction or get it
formatted into the cell as a fraction
Here is the formula

Private Sub ListBox2_Change()
If ListBox2.Value = "H.W.R." Then
Label17.Caption = TextBox4.Text - 1 / 8
Else
Label17.Caption = TextBox4.Text + 1 / 8
End If

End Sub

Any Ideas
Click to expand...

Do you want it as a mixed fraction or an improper fraction?
 
D

damorrison

preferably 1/2, 1/4, 1/8,1/16,
But if I have to settle for /16's that's what I'll do
 
D

damorrison

"Label17.Caption = Application.Text(cdbl(TextBox4.Text) - 1 / 8,
"??/??") "..

That doesn't work for me all I get is the number in fractions such as 7
1/8 I get 57/8
 
D

damorrison

"Label17.Caption = Application.Text(cdbl(TextBox4.Text) - 1 / 8,
"??/??") "..

That doesn't work for me all I get is the number in fractions such as 7
1/8 I get 57/8
 
D

Dave Peterson

Label17.Caption = Application.Text(CDbl(a) - 1 / 8, "# ??/??")
or
Label17.Caption = Application.Text(CDbl(a) - 1 / 8, "# ??/16")
 
P

Paul Lautman

damorrison said:
preferably 1/2, 1/4, 1/8,1/16,
But if I have to settle for /16's that's what I'll do
Click to expand...

I don't understand. If the fraction that needs to be displayed is say 5/16
wouldn' that be what you want?

But you didn't answer my question.

If I ask Excel to display 43.25 and format the cell as Fraction it'll
display it as a mixed number rather than an improper fraction. Is that what
you want? If so I find that the cell format works fine for me. What happens
when you put a number into a cell formatted as Fraction?

Please try to give full information so that I don't have to keep guessing
 
P

Paul Lautman

damorrison said:
"Label17.Caption = Application.Text(cdbl(TextBox4.Text) - 1 / 8,
"??/??") "..

That doesn't work for me all I get is the number in fractions such as
7 1/8 I get 57/8
Click to expand...

Maybe it would help if you gave us an example of an input and the required
output. Your descriptions of what you want and what you are getting are
sparse and incomplete.
 
D

damorrison

Right on, sorry about that, I suppose I want a proper fraction, with
the whole number plus the fraction



'Label17.Caption = Application.Text(CDbl(a) - 1 / 8, "# ??/16") '

Seems to be working, it shows up on the userform
 
P

Paul Lautman

damorrison said:
Right on, sorry about that, I suppose I want a proper fraction, with
the whole number plus the fraction



'Label17.Caption = Application.Text(CDbl(a) - 1 / 8, "# ??/16") '

Seems to be working, it shows up on the userform
Click to expand...

That will give you a mixed number.
 
Top