make a simple multiplication in a textbox on a userform

tompie007 · Jan 17th 2017

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.

TheSilkCode · Jan 17th 2017

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.

gijsmo · Jan 22nd 2017

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:

Code

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).

make a simple multiplication in a textbox on a userform

Participate now!

Share