New Excel Forum

This forum has been moved to TeachExcel.com

Ask all future questions in the New Excel Forum.

ExcelKey

Formatting on userform textbox

Macros, VBA, Excel Automation, etc.

Formatting on userform textbox

Postby Apcbr » Sun May 20, 2012 10:43 pm

Hi,

I'm trying to format a textbox to show fractions using the following
Code: Select all
UserForm1(Box_Name) = Format(UserForm1(Box_Name), "# ??/??")

When I run it, it show something like 100 ??/?? instead of 99 3/4 (it rounds up)

I've used "(000) 000-0000" to format phone numbers, "MM/DD/YYYY" for dates, "0.00" for numbers with 2 decimals, they all work. Does anyone know what's wrong with the fraction?

Thanks
  • 0

Greetings,
Apcbr

Say what you mean, mean what you say :-)
Apcbr
Regular
 
Posts: 38
Joined: Apr 25, 2012
Reputation: 0
Excel Version: 2007

Re: Formatting on userform textbox

Postby Sisyphus » Mon May 21, 2012 5:33 am

Hello Acpbr,
I did some research on this and came up empty. It seems that the Format function doesn't support fractions. "??/??" only works as a numberformat for cells. I suppose if you must have a proper fraction in the textbox you might try to write the decimal fraction to an appropriately formatted cell of your worksheet and then try to lift the result as text. I don't know how this might work but that is what you might try in the absence of a better idea.
  • 0

Have a great day! :D

Sisyphus
I do this for "honour and country" - much less of the latter, actually.
If I helped you, award points, plenty of them.
If I bored you, deduct points for being too long-winded. (I know, :lol)
Sisyphus
Former Moderator
 
Posts: 4454
Joined: Dec 7, 2011
Location: Shanghai
Reputation: 203
Excel Version: 2010

Re: Formatting on userform textbox

Postby Apcbr » Mon May 21, 2012 6:40 am

Thanks Sisyphus,

I was thinking that the fractions were not supported :( .

it's easy to write to and from a formatted cell but that doesn't work either 99 3/4 is still just 99.75

An other idea I had was to look for a decimal point in the textbox and then replace the numbers after the decimal point with fractions using case select. That should work, but it would be a list of 16 fractions (1/2, 1/4, 1/8, or n/16)
I'll try that later today, thanks
  • 0

Greetings,
Apcbr

Say what you mean, mean what you say :-)
Apcbr
Regular
 
Posts: 38
Joined: Apr 25, 2012
Reputation: 0
Excel Version: 2007

Re: Formatting on userform textbox

Postby Sisyphus » Tue May 22, 2012 4:18 am

Hi Apcbr,
Your requirements are so modest I felt invited to create this solution:
Code: Select all
Option Explicit

Private Sub test()
    Debug.Print ReducedFraction(13.24)
End Sub

Public Function ReducedFraction(ByVal Num As Double) As String

    Const HiD As Long = 128          ' Highest denominator
   
    Dim M As Long
    Dim n As Long
    Dim d As Long
   
    M = Int(Num)
    n = Round((Num - M) * HiD, 0)
    d = HiD
    Do While (n Mod 2 = 0)
        n = n / 2
        d = d / 2
    Loop
    ReducedFraction = CStr(M) & " " & CStr(n) & "/" & CStr(d)
End Function

You can put the string in your Textbox.
Code: Select all
UserForm1(Box_Name) = ReducedFraction(UserForm1(Box_Name))

Note that the decimal is rounded to the nearest HiD (Constant). Test the decimal 0.24 with HiD = 128, 64 and 32. You will get different results because of the rounding.
  • 0

Have a great day! :D

Sisyphus
I do this for "honour and country" - much less of the latter, actually.
If I helped you, award points, plenty of them.
If I bored you, deduct points for being too long-winded. (I know, :lol)
Sisyphus
Former Moderator
 
Posts: 4454
Joined: Dec 7, 2011
Location: Shanghai
Reputation: 203
Excel Version: 2010

Re: Formatting on userform textbox

Postby Apcbr » Tue May 22, 2012 7:02 am

Neat :D
Thanks Sisyphus,
That is way more elegant than a list of 15 possible fractions.

I've changed the max denominator to 16, the fractions will only range from 1/16 to 15/16. We use it in a cut sheet for aluminum lengths, I used to have a list that showed what each decimal was in a fraction. It's really hard to cut something as 112.9375. 112 15/16 is easier to read on a measuring tape :-).

If only the US would go metric, my live would be so much easier :-)
  • 0

Greetings,
Apcbr

Say what you mean, mean what you say :-)
Apcbr
Regular
 
Posts: 38
Joined: Apr 25, 2012
Reputation: 0
Excel Version: 2007

Re: Formatting on userform textbox

Postby Apcbr » Tue May 22, 2012 7:54 pm

The final coding.
Code: Select all
Private Sub Fraction_Btn_Click()
    Dim x, y As Integer

    For y = 2 To 11 'First box is never a decimal
        For x = 1 To 10
            Box_Name = "X" & x & "Y" & y
            If IsNumeric(UserForm1(Box_Name)) Then 'some boxes are "n/a"
            UserForm1(Box_Name) = ReducedFraction(UserForm1(Box_Name))
            End If
        Next x
    Next y
End Sub

Public Function ReducedFraction(ByVal Num As Double) As String
    Const HiD As Long = 32          ' Highest denominator
    Dim M As Long
    Dim n As Long
    Dim d As Long
   
    M = Int(Num)
    n = Round((Num - M) * HiD, 0)
    d = HiD
    If n > 0 Then 'No decimal will have n as 0
        Do While (n Mod 2 = 0)
            n = n / 2
            d = d / 2
        Loop
        ReducedFraction = CStr(M) & " " & CStr(n) & "/" & CStr(d)
    Else
        ReducedFraction = CStr(M)
    End If
   
End Function

I had to add some code to intercept the whole numbers, they caused the loop to become permanent. I probably should have mentioned that not all textboxes have numeric values or decimals.
Anyway, it works great now, thank you so much for the code and the new approach to decimals and fractions :D
  • 0

Greetings,
Apcbr

Say what you mean, mean what you say :-)
Apcbr
Regular
 
Posts: 38
Joined: Apr 25, 2012
Reputation: 0
Excel Version: 2007

Re: Formatting on userform textbox

Postby Sisyphus » Sat May 26, 2012 4:44 am

Hi Apcbr,
Your are quite right, my function doesn't handle whole numbers correctly, and thank you for pointing this out. I have found that it also doesn't handle negative numbers and those where the decimals are rounded to the set highest denominator. For example, 9.99999 would return 9 64/64. All of the above I have corrected in this version:
Code: Select all
Public Function ReducedFraction(ByVal Num As Double) As String

    Const HiD As Long = 64          ' Highest denominator
   
    Dim Rf As String
    Dim M As Long
    Dim n As Long
    Dim d As Long
   
    M = Int(Abs(Num))
    n = Round((Abs(Num) - M) * HiD, 0)
    If n Then
        If n = HiD Then
            M = M + 1
        Else
            d = HiD
            Do While (n Mod 2 = 0)
                n = n / 2
                d = d / 2
            Loop
            Rf = " " & CStr(n) & "/" & CStr(d)
        End If
    End If
    ReducedFraction = CStr(M * Sgn(Num)) & Rf
End Function
  • 0

Have a great day! :D

Sisyphus
I do this for "honour and country" - much less of the latter, actually.
If I helped you, award points, plenty of them.
If I bored you, deduct points for being too long-winded. (I know, :lol)
Sisyphus
Former Moderator
 
Posts: 4454
Joined: Dec 7, 2011
Location: Shanghai
Reputation: 203
Excel Version: 2010

Re: Formatting on userform textbox

Postby Apcbr » Sun May 27, 2012 9:59 pm

Thank you Sisyphus,

The changes you made do take care of the whole numbers and such.
I won't encounter negative numbers or numbers below 50 or so, but i did notice numbers between 0 and 1, like 0.75 show as 0 3/4 and not 3/4. However, for my purposes it works great :-)
I also have the option to go back to decimals. If you feel like playing with that... ;)
The best I could come up with was adding all textbox values to a two dimensional array before converting to fractions, then when I need the decimals back just using the array to re-populate the boxes. :roll:
Anyway, like I said, the program works as I want it to, but if you feel up to a challenge..how to get the fractions back to decimals.
  • 0

Greetings,
Apcbr

Say what you mean, mean what you say :-)
Apcbr
Regular
 
Posts: 38
Joined: Apr 25, 2012
Reputation: 0
Excel Version: 2007

Re: Formatting on userform textbox

Postby Sisyphus » Sun May 27, 2012 11:03 pm

Hello Apcbr,
No amount of theory can replace practical experience!
Thank you, again, for pointing out the flaw. I have fixed it:
Code: Select all
Public Function ReducedFraction(ByVal Num As Double) As String

    Const HiD As Long = 64          ' Highest denominator
   
    Dim Rf As String
    Dim M As Long
    Dim n As Long
    Dim d As Long
   
    M = Int(Abs(Num))
    n = Round((Abs(Num) - M) * HiD, 0)
    If n Then
        If n = HiD Then
            M = M + 1
        Else
            d = HiD
            Do While (n Mod 2 = 0)
                n = n / 2
                d = d / 2
            Loop
            Rf = " " & CStr(n) & "/" & CStr(d)
        End If
    End If
    M = M * Sgn(Num)
    If Len(Rf) Then
        If M Then Rf = CStr(M) & Rf
    Else
        Rf = CStr(M)
    End If
    ReducedFraction = Rf
End Function

I also thought about converting the fraction to decimals. It isn't very difficult. Instead, the problem is that the value gets changed when converting to and fro due to the rounding done in the above function. The decimal is definitely more precise. So, for maximum precision you should definitely add the decimals and convert only the final sum. With that being said, here is the function that does the reversing.
Code: Select all
Public Function FractionToDecimal(ByVal Num As String) As Variant
   
    Dim Fd As Variant
    Dim S() As String
    Dim Dn As Double
   
    S = Split(Num)
    If LBound(S) = UBound(S) Then
        Fd = Num
    Else
        Fd = Val(S(LBound(S)))
        S = Split(S(UBound(S)), "/")
        If UBound(S) > LBound(S) Then
            Dn = Val(S(UBound(S)))
            If Dn = 0 Then Dn = 1
            Fd = Val(S(LBound(S))) / Dn * Sgn(Fd) + Fd
        End If
    End If
    FractionToDecimal = Fd
End Function
Let me know if you find something to improve it. :lol:
  • 0

Have a great day! :D

Sisyphus
I do this for "honour and country" - much less of the latter, actually.
If I helped you, award points, plenty of them.
If I bored you, deduct points for being too long-winded. (I know, :lol)
Sisyphus
Former Moderator
 
Posts: 4454
Joined: Dec 7, 2011
Location: Shanghai
Reputation: 203
Excel Version: 2010

Re: Formatting on userform textbox

Postby Apcbr » Sun May 27, 2012 11:58 pm

wow, that was quick. :D
I knew you'd like a challenge. I'll try it tomorrow and see what it does :-)
  • 0

Greetings,
Apcbr

Say what you mean, mean what you say :-)
Apcbr
Regular
 
Posts: 38
Joined: Apr 25, 2012
Reputation: 0
Excel Version: 2007

Next

Return to Macros and VBA Questions

Who is online

Users browsing this forum: Google [Bot] and 27 guests