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:

  1. Option Explicit
  2. Private Sub TextBox1_Change()
  3. If Not IsNumeric(TextBox1.Value) Then
  4. MsgBox "Only numbers are allowed"
  5. Cancel = True
  6. End If
  7. End Sub

Description:

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.

 

You can find similar Excel Questions and Answer hereunder

1) Write to text file without quotes in vba in Excel

2) Line break in vba message box in Excel

3) How do I copy a Table from one location to another and retain all formulas, formats and columnwidths?

4) How can I shade alternate rows in color using VBA to make it easier to read voluminous data running into hundreds of rows?

5) How can I save a WorkSheet as a new WorkBook using VBA?

6) How can I add Trendline to a chart using VBA?

7) How can I change the Marker size and Marker line color for all the series in a chart?

8) How can I remove display of Gridlines in my worksheet using VBA?

9) How do I assign a macro to a shape using VBA?

10) Vba clear the contents of an entire sheet in Excel

11) How can I add a Timestamp after macro execution?

12) How can I export a chart as a gif file?

13) I have a macro that takes a lot of time for execution - how can I keep the user informed that the macro is running?

14) How can I have text autocomplete based on values previously entered in Column?

15) How can I activate a routine when there is a change in value of a cell?