Another UF question -SOLVED!!!!!

[mortgageman]

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

 

[Legacy 98055]

Am waiting to see how Joe's solution works for you before I reply.

 

[mortgageman]

Ok - Using Joe's code is a bad news/good news situation. I can put my cursor in the textbox and hit the change button and that textbox will change, (the "Enter" event - I think) but if the cursor is NOT in the textbox, then hitting the change button only changes one, not all four. It is always textbox25 that changes BTW. I should point out that since all the textboxes are part of one matrix, I will almost never want to change just one textbox at a time. I am posting the code in case I did something really stupid, but I don't think I did. I just took Joes' code and changed it for the textboxes that I was interested in.

BTW - and I don't know if this matters - but the textboxes (that I am interested in) are usually not changed by the user, they are changed by my code. The user puts in the initial matrix and the code calculates the inverse matrix and populates the textboxes.

Gene, "The Mortgage Man", Klein



Private Sub TextBox21_Change()
'UserForm module code, only!

myCtrlTB = "TextBox21"
End Sub

Private Sub TextBox22_Change()
'UserForm module code, only!

myCtrlTB = "TextBox22"
End Sub

Private Sub TextBox24_Change()
'UserForm module code, only!

myCtrlTB = "TextBox24"
End Sub

Private Sub TextBox25_Change()
'UserForm module code, only!

myCtrlTB = "TextBox25"
End Sub
Private Sub TextBox21_Enter()
'UserForm module code, only!

myCtrlTB = "TextBox21"
End Sub


Private Sub TextBox22_Enter()
'UserForm module code, only!

myCtrlTB = "TextBox22"

End Sub
Private Sub TextBox24_Enter()
'UserForm module code, only!

myCtrlTB = "TextBox24"
End Sub


Private Sub TextBox25_Enter()
'UserForm module code, only!

myCtrlTB = "TextBox25"

End Sub








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

Dim myTB As Control

On Error GoTo myOther

Set myTB = Controls.Item(myCtrlTB)

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

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

myOther:
On Error GoTo myErr

myVal = myTB.Value
myTB.Value = Application.WorksheetFunction.Text(myVal, "# ??/??")
GoTo myEnd

myErr:
MsgBox "Please Enter a number or fraction!", vbCritical + vbOKOnly, "Error with number!"

myEnd:
End Sub

 

[Joe Was]

The code will only react to the user. It remembers the last TextBox changed by the user or the TextBox the User put the cursor in. To get the code to change all TextBoxs you need to use a For - Next Loop. In your original post you should have stated the whole problem. Its bad enough half way through you added additional TextBoxs to the problem. The code will need to be re-writen to work on a collection. That is a completly different problem.

Please state exactly what you have and what you want to end up with. I think we can do a re-write.

 

[Joe Was]

OK, This code will re-format each TextBox as a Collection. All TextBoxs formats will be toggled at the same time, each time the Command Button is clicked.

You do not need any of the Enter or Change Events for this code to work.


Private Sub CommandButton2_Click()
'UserForm module code, only!
Dim myMatrixTB As Control, myTB As Control
Dim myVal$, myNum$, myDenom$
Dim myNumLen%, myCnt%
Dim myValResult As Variant

For Each myMatrixTB In Controls
If (myMatrixTB.Name Like "TextBox*") Then

On Error GoTo myOther

myVal = myMatrixTB.Value
If IsNumeric(myVal) = False Then
myNumLen = Application.WorksheetFunction.Search("/", myVal)
myNum = Left(myVal, myNumLen - 1)
myCnt = Len(myVal)
myDenom = Right(myVal, myCnt - myNumLen)
myValResult = myNum / myDenom

myMatrixTB.Value = Application.WorksheetFunction.Text(myValResult, "#,##0.000")
GoTo myEnd
Else
GoTo myOther
End If

myOther:

myMatrixTB.Value = Application.WorksheetFunction.Text(myVal, "# ??/??")

myEnd:
Else
End If
Next myMatrixTB

End Sub


And, as your TextBoxs will not be updated by the user, if you use the For - Next Loop, as above, and Tom's code, you could make the toggle more compact and efficent. My longer code was to trap user input errors, which you do not need any more!

 

[mortgageman]

Sorry for all the trouble Joe - but I guess to a novice like myself, it is sometimes not obvious what is relevant and what is not. In my case, I didn't realize that software changing vs user changing makes a difference in how the event handler reacts. I understand now that it is the LAST change that matters - so in my case it was the last textbox that the software changed that would always change. 20 20 hindsight I guess.

Problem - your latest code bombs on "Unable to get Search property of the worksheetfunction class." Which is really strange, since the search is in the list - I checked.

Gene, "The Mortgage Man", Klein

 

[mortgageman]

SOLVED!!!!!!. How did I do it? Easy - just use the best of Tom, Joe and Norie. Talk about standing on the shoulders of giants! (with me NOT being Newton of course).

Here is the code. Thanks again to all

Gene, "The Mortgage Man", Klein




Private Sub CommandButton1_Click()
Dim myVal, myNumLen
If CommandButton1.Caption = "Inverse Matrix: Fraction" Then
TextBox21 = Evaluate("TEXT(" & Val(TextBox21) & ",""# ??/??"")")
TextBox22 = Evaluate("TEXT(" & Val(TextBox22) & ",""# ??/??"")")
TextBox24 = Evaluate("TEXT(" & Val(TextBox24) & ",""# ??/??"")")
TextBox25 = Evaluate("TEXT(" & Val(TextBox25) & ",""# ??/??"")")
CommandButton1.Caption = "Inverse Matrix: Decimal"
Else
TextBox21 = Evaluate("TEXT(" & frac2dec(TextBox21.Value) & ",""#,##0.00"")")
TextBox22 = Evaluate("TEXT(" & frac2dec(TextBox22.Value) & ",""#,##0.00"")")
TextBox24 = Evaluate("TEXT(" & frac2dec(TextBox24.Value) & ",""#,##0.00"")")
TextBox25 = Evaluate("TEXT(" & frac2dec(TextBox25.Value) & ",""#,##0.00"")")
CommandButton1.Caption = "Inverse Matrix: Fraction"
End If
End Sub


Private Function frac2dec(fracstr) As Double
Dim myNumLen, myNum, myCnt, myDenom As Integer
myNumLen = Application.WorksheetFunction.Search("/", fracstr)
myNum = Left(fracstr, myNumLen - 1)
myCnt = Len(fracstr)
myDenom = Right(fracstr, myCnt - myNumLen)
frac2dec = myNum / myDenom
End Function

 

[Legacy 98055]

Hurray!