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
 

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.

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
76,303
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
 

Forum statistics

Threads
1,141,155
Messages
5,704,626
Members
421,360
Latest member
Rhodia

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top