hi,
would it be possible to make a multiplication directly in a text box on userform? For example if I type 17*60 and then enter or tab to go to the next text box it is transformed in 1020?
Thanks,
Tom.
hi,
would it be possible to make a multiplication directly in a text box on userform? For example if I type 17*60 and then enter or tab to go to the next text box it is transformed in 1020?
Thanks,
Tom.
Re: make a simple multiplication in a textbox on a userform
No this would not work- Excel UserForms' textboxes are truly textboxes so the UserForm would treat that as a string of "17*60" unless you added special formatting to the change event.
Re: make a simple multiplication in a textbox on a userform
One way to "trick" the Textbox into being an inline calculator is to copy the value of the textbox to an unused cell on the underlying worksheet and use Excel itself to calculate the value.
This can be done a number of ways using the Textbox Exit handler. Here is an example of one way to do this:
Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean) Dim ws As Worksheet
Dim rLast As Range
'copy the text from the text box to a cell as if it was a formula
'there are many ways to do this, one way is to create a temporary worksheet
'a simpler way is to use a cell outside the usedrange to hold the formula
'you could in theory calculate a global value for rLast in eg, the Userform Initialize code
'if the UsedRange won't change during the life of this UserForm
'assume the workbook contains 1 sheet and use the first sheet
Set ws = ThisWorkbook.Sheets(1)
'get an unused cell outside the used range
Set rLast = ws.UsedRange.Cells(ws.UsedRange.Rows.Count + 1, ws.UsedRange.Columns.Count + 1)
'put in some error handling so that if an invalid
'formula is in the textbox, it will not accept the value
With rLast
On Error Resume Next
'the unused cell will be used to interpret the calculation formula in the textbox
.Formula = "=IFERROR(" & TextBox1.Value & ","""")"
On Error GoTo 0
If .Value <> "" Then
TextBox1.Value = .Value
Else
'throw in a beep if required
Beep
'alternatively uncomment the next line to clear the textbox in case of error
'TextBox1.Value = ""
'and stop the user from moving off the field
Cancel = True
End If
'remove the formula from the temporary cell used
.ClearContents
End With
End Sub
Display More
The advantage of this is Excel does all the hard work of determining if the value is a valid calculation.
There is a disadvantage if you want multiplication to be the *only* form of calculation you will accept. A lot more parsing of the text would be required before letting Excel calculate it.
This is one method that will work with any valid calculation that you can put into Excel including complex mathematical formulas (assuming the Textbox 'formula' the user types will fit within any MaxLength value you have set for the Textbox).
Don’t have an account yet? Register yourself now and be a part of our community!