• 0

VBA Excel Textbox to accept numbers only?


Question

OK guys, I want to have a few textboxes that only accept numerical chars, not letters. Therefore I need some sort of validation as soon as the user has entered something into the textboxes to prevent this. I have demonstrated this below in a basic way, whereby if the user enters a letter ?a? then they will get an error message, however it will still leave the letter a in the textbox which is no good. Plus I need it for all the alphabet, not just ?a?. There must be an easier way todo what I require, can anyone help?

Private Sub TextBox2_Change()

If TextBox2 = ?a? Then MsgBox "Enter Numerical characters only "

5 answers to this question

Recommended Posts

  • 0
  rageagainstmachine said:
OK guys, I want to have a few textboxes that only accept numerical chars, not letters. Therefore I need some sort of validation as soon as the user has entered something into the textboxes to prevent this. I have demonstrated this below in a basic way, whereby if the user enters a letter ?a? then they will get an error message, however it will still leave the letter a in the textbox which is no good. Plus I need it for all the alphabet, not just ?a?. There must be an easier way todo what I require, can anyone help?

Private Sub TextBox2_Change()

If TextBox2 = ?a? Then MsgBox "Enter Numerical characters only "

This looks helpful, Numeric Validation

Also, this may put you on the right path if the aforementioned link comes to no help.

Good luck,

SilverB.

  • 0

The following is perfect, exactly what i need, however, it errors when a "." is entered on its own. where it should, for example

replace that single"." with a "0" so how can that code be corrected so that is what happens? The author of that code was trying

To exaclty that!

Thanks for al lyour help guys

Private Sub TextBox1_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)

Select Case KeyAscii

Case Asc("0") To Asc("99")

Case Asc(".")

If InStr(1, TextBox1.Text, ".") > 0 Then

KeyAscii = 0

End If

Case Else

KeyAscii = 0

End Select

End Sub

  • 0

I got it working pretty much how I wanted by adding another private sub. Any comments appreciated

Private Sub TextBox2_Change()

If TextBox2 = "." Then TextBox2 = "0"

End Sub

'Prevents Users from entering Letter chars into numerical textboxes

Private Sub TextBox2_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)

If TextBox2 = "." Then TextBox2 = "3"

If InStr(1, TextBox2.Text, ".") > 0 And KeyAscii = Asc(".") Then

KeyAscii = 0

Exit Sub

End If

Select Case KeyAscii

Case Asc("0") To Asc("9"), Asc(".")

Case Else

KeyAscii = 0

End Select

End Sub

This topic is now closed to further replies.
  • Recently Browsing   0 members

    • No registered users viewing this page.