vba restrict activex textbox to numeric values in Excel
Q193. In Excel, how do I restrict user entry in ActiveX Text Box to just numeric values?
For example, I have a ActiveX Text Box (TextBox1) - I want to prevent users from entering anything other than numbers in this TextBox.
To do it in Excel, here is the answer:
- Option Explicit
- Private Sub TextBox1_Change()
- If Not IsNumeric(TextBox1.Value) Then
- MsgBox "Only numbers are allowed"
- Cancel = True
- End If
- End Sub
a) Line 1 - Whenever there is a change in TextBox Value, the code in routine is executed.
b) Line 2 - Check if Input is a numeric value.
c) Line 3 - If Input is not a numeric value, prompt user.
Note: The code above has to reside in the Sheet corresponding to where the ActiveX control resides. For example, if TextBox1 ActiveX Control is in Sheet1, the code should reside in Sheet1 in VBA Module.