VBA won't format into fraction
 

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

VBA won't format into fraction
 

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

VBA won't format into fraction
 

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

VBA won't format into fraction
 

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?

VBA won't format into 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

VBA won't format into fraction
 

"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

VBA won't format into fraction
 

"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

VBA won't format into fraction
 

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

VBA won't format into fraction
 

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

VBA won't format into fraction
 

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.

VBA won't format into fraction
 

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

VBA won't format into fraction
 

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.