Forum Discussion
How do we capture numeric input from an Excel Userform textbox?
We are using a textbox in an Excel Userform to request numeric input from the user. The two userform text boxes request numbers then used to set the width and height of a new checkbox form control on a worksheet. We validate the user input for the Userform text boxes as numeric, where TextBoxStringWidth and TextBoxStringWidth represent the input from the userform text boxes, as follows:
If Not IsNumeric(TextBoxStringWidth) Or _
Not IsNumeric(TextBoxStringHeight) Then
MsgBox ("CANNOT PROCESS!! User input for height or width " _
& "is a non-numeric value")
GoTo EndResize
Exit Sub
End If
The validation routine above appears to work, but when we try to assign the textbox numeric input as the values for the width and height of the new checkbox control, it does not work as follows:
CurrentCheckbox.Width = TextBoxStringWidth (have also tried TextBoxStringWidth.Value)
CurrentCheckbox.Height = TextBoxStringHeight (have also tried TextBoxStringHeight.Value)
Unfortunately, the VBA watch window indicates that the values for the input boxes are text (for example, “10”) and not apparently non-numeric as far as the setting values for the new checkbox width and height parameters.
We have looked up many articles and they all give very complex solutions that appear unnecessary for such an easy operation. Does anyone know how we can change these textual numbers (numbers within quotes) into plain numbers so they can be provided as values for the width and height of the checkbox? Thank you for any assistance.
The code looks OK. See the attached demo workbook.
Have you defined CurrentCheckBox correctly?
Hello Hans, thank you so much again for the effort you went to in order to provide a sample using our exact userform control names. It was extremely easy to follow that way and helpful in confirming that the code we were using for resizing the checkbox should have been working fine. Your short example left us comfortable in redirecting our attention elsewhere looking for the problem. In our case, selecting the option did nothing, so we suspect there is some other mistake that is aborting the operation, before it is performed, without any error messages. Thank you again for taking the time to confirm the code was working fine.
In response to your question, yes, we have properly defined CurrentCheckbox. In fact, if we specify constant values such as CurrentCheckBox.Width = 40 and CurrentCheckBox.Height =20 in this routine, everything works fine. The problem seems to be that the user values we are requesting from the userform text boxes are being perceived as text versus numbers, even though they pass the validation test above as a numerical value. Is there some type of statement that will turn a text number into a numeric value before assigning the values to the width and height fields of the checkbox?
