Another UF question -SOLVED!!!!!

mortgageman

Well-known Member
Joined
Jun 30, 2005
Messages
2,015
Okay here we go again :wink:

In the ongoing saga that is the WIP of my daughters HW helper, I have just added a Inverse Matrix. What I do is present the elements of the matrix in TextBoxes. By default, the results are shown in decimal. What I would like to have is a button on the UF that would allow the user to toggle between decimal format and fraction format. Is this out of this world difficult or just mildly so?

TIA for any help anyone offers

Gene, "The Mortgage Man", Klein
 

Some videos you may like

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand

Joe Was

MrExcel MVP
Joined
Feb 19, 2002
Messages
7,539
Sub toggleFormat()
'Standard module code, like: Module1!

If Selection.NumberFormat = "#,##0.000" Then
Selection.NumberFormat = "# ??/??"
Else
Selection.NumberFormat = "#,##0.000"
End If
End Sub
 

mortgageman

Well-known Member
Joined
Jun 30, 2005
Messages
2,015
Thanks Joe - I don't know why I thought it would be a lot more complicated.

Gene, "The Mortgage Man", Klein
 

mortgageman

Well-known Member
Joined
Jun 30, 2005
Messages
2,015
Ok - now I remember why I thought this would be more difficult (it was late last night when I first posted). Numberformat (or format or anything like it) does not appear in the properties window of the UF design area. I had thought it was an inclusive list.

Gene, "The Mortgage Man", Klein
 

mortgageman

Well-known Member
Joined
Jun 30, 2005
Messages
2,015

ADVERTISEMENT

Joe - I don't think your way will work. I got a error of method not found when I run it. Are you sure this will work in a User Form Text Box?

Gene, "The Mortgage Man", Klein
 

Joe Was

MrExcel MVP
Joined
Feb 19, 2002
Messages
7,539
No the code was for a sheet cell. Same idea though. I will see if I can work-up the code for a textbox, it's real busy here today!
 

mortgageman

Well-known Member
Joined
Jun 30, 2005
Messages
2,015

ADVERTISEMENT

Take your time neighbor, (I'm south east of you - I guess), I certainly appreciate the help. I'm curious to learn how you will get around the fact that format doesn't seem to be a property of the textbox. (Other than writing it to a cell and reading it back in)

Gene, "The Mortgage Man", Klein
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
75,911
Office Version
  1. 365
Platform
  1. Windows
Gene

Try this.
Code:
TextBox1 = 1.5
TextBox1 = Evaluate("TEXT(" & Val(TextBox1) & ",""# ??/??"")")
 

mortgageman

Well-known Member
Joined
Jun 30, 2005
Messages
2,015
FWIW:

I just tried this: TextBox21.value=Format(Textbox21.value,"# ??/??")

and all it did was put the actual string ??/?? in the textbox.

Oh well - I have always been much better at finding out what does not work.

Gene, "The Mortgage Man", Klein
 

Joe Was

MrExcel MVP
Joined
Feb 19, 2002
Messages
7,539
I tested this it works, it will toggle the value format in a UserForm TextBox, it runs from one Command Button. You can enter the initial value into the textbox as 0.000 [or any variant of] or nn/dd and the button code will convert it to the other format, hit it again and it will convert it back:


Private Sub CommandButton1_Click()
'UserForm module code!
Dim myVal$, myNum$, myDenom$
Dim myNumLen%, myCnt%
Dim myValResult As Variant

On Error GoTo myOther

myVal = TextBox1.Value
myNumLen = Application.WorksheetFunction.Search("/", myVal)
myNum = Left(myVal, myNumLen - 1)
myCnt = Len(myVal)
myDenom = Right(myVal, myCnt - myNumLen)
myValResult = myNum / myDenom

TextBox1.Value = Application.WorksheetFunction.Text(myValResult, "#,##0.000")
GoTo myEnd

myOther:
myVal = TextBox1.Value
TextBox1.Value = Application.WorksheetFunction.Text(myVal, "# ??/??")

myEnd:
End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,111,910
Messages
5,541,538
Members
410,547
Latest member
htran4
Top