Formatting textbox
This should be easy but escapes me. How do I format a textbox on a user form so that the text will read as a fraction. I use a slider/ textbox combo. Then the code divides the slider amount by 32. I want the result to read as 7/32" etc.
Thanks again for being so on top of things and willing to help us neophites out here!
Last edited by blue9244; 12-18-2008 at 08:36 PM.
If you want to round it to the nearest 1/32, thentextbox1.value = format(somenumber, "# ??/??")
textbox1.value = format(round(somenumber * 32, 0) / 32, "# ??/??")
Microsoft MVP - Excel
Entia non sunt multiplicanda sine necessitate
This code should work for you
Check out the sample workbookPrivate Sub TextBox1_Change() If IsNumeric(TextBox1.Value) Then Label1.Caption _ = Application.Text(Round(TextBox1.Value / 0.03125, 0) * 0.03125, "# ??/??") Else Label1.Caption = "not a number in textbox1" End If End Sub
http://www.freewebs.com/davesexcel/f...20userform.xls
Thanks so very much! That ought to do it!
I think I marked this one as solved a little too soon. All I'm getting in the text box is: ??/?? and no value... If I leave the code just as you depicted it, I get an interger with ??/?? as a suffix. Here is what I have:
Thanks again. I'm sure I'm just missing something very obvious to you guys!Private Sub FingerOpen1_Change() Dim FingerOpenVal1 As Variant FingerOpenVal1 = Val(FingerOpen1.Text) If FingerOpenVal1 >= ScrollBar16.Min And FingerOpenVal1 <= ScrollBar16.Max Then ScrollBar16.Value = FingerOpenVal1 End If End Sub Private Sub ScrollBar16_Change() FingerOpen1.Value = Format(Round(ScrollBar16.Value * 32, 0) / 32, "# ??/??") End Sub
BTW, why would you multiply by 32 only to divide by 32 - doesn't that leave you where you started?
Could someone please tell me where my error is in the above code? I've tried everything I can think of and nothing works. I'm sure your examples were fine - I just must have missed something along the way.
Please and thanks very much!
We don't have your form or workbook, so debugging is up to you. When you step through the ScrollBar16_Change() code, what's the value of ScrollBar16?
If you mutiply by N, round to a whole number, and divide by N, the result is rounded to the nearest multiple of 1/N.why would you multiply by 32 only to divide by 32 - doesn't that leave you where you started?
Microsoft MVP - Excel
Entia non sunt multiplicanda sine necessitate
I didn't answer quickly because I was attempting to think this through myself but I'm still at a loss. The value of the ScrollBar changes as the user slides the scroll bar. At one point I had it at 108 and stepped through the procedure dividing by 32, I obtained the value I wanted - i.e. 3.375. The text box returns the value as 3.375 correctly. I want it to read 3 3/8 in this case. When I apply the formatting, I get either a type mis-match or the result in the text box as 108 ??/?? depending on where I've tried to apply the formatting.
Here's the code to obtain 3.375 (or any other number selected by the scroll bar).
Where FingerOpen1 is Textbox1. Does this help at all?Private Sub FingerOpen1_Change() Dim FingerOpenVal1 As Variant FingerOpenVal1 = Val(FingerOpen1.Value) If FingerOpenVal1 >= ScrollBar16.Min And FingerOpenVal1 <= ScrollBar16.Max Then ScrollBar16.Value = FingerOpenVal1 End If End Sub Private Sub ScrollBar16_Change() FingerOpen1.Value = ScrollBar16.Value / 32 End Sub
Huh; who knew Format wouldn't do fractions?
Change Format to WorksheetFunction.Text
Microsoft MVP - Excel
Entia non sunt multiplicanda sine necessitate
Hi
I've got a funny feeling that FORMAT doesn't like the fraction component.
try something like
EDIT: Darn - shg you beat me to it...FingerOpen1.Value = evaluate("=text(" & ScrollBar16.Value / 32 & ",""# ??/32"")")
rylo
That did it!!!! Thanks so very much!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks