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

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)

"damorrison" wrote in message
ups.com...
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

Maybe something like:

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

damorrison wrote:

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

--

Dave Peterson

damorrison wrote:
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

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

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

"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

"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

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

damorrison wrote:

"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

--

Dave Peterson

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

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

damorrison wrote:
"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

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.

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

damorrison wrote:
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

That will give you a mixed number.