Data Verification in VBA (Visual Basic in Excel)

gvblake22 · Mar 25, 2006

Ok, I have a text box on a user form and I need to verify that the user is only typing in a number. So like if they typed in a letter, a message box would pop up and say that it needs to be a number. I know it needs to be in the _change() event, but what code do I write to verify that the character being entered is a number? I just can't get anything to work! :bang head

Oh, this is for a user form created in Microsoft Excel...

Any and all help is greatly appreciated :santa:

seadave77 · Mar 25, 2006

Code:

Private Sub Text1_Change()

If Len(Text1.Text) = 0 Then Exit Sub

If Asc(Right(Text1.Text, 1)) < 48 Or Asc(Right(Text1.Text, 1)) > 57 Then
    MsgBox "Must be a number."
End If

End Sub

This checks the ascii code of the last letter typed. If it is not an ascii code for a number it prompts the user.

I haven't forgotten about your calander by the way, I just got real busy the past couple of days.

gvblake22 · Mar 25, 2006

seadave77 said:
This checks the ascii code of the last letter typed. If it is not an ascii code for a number it prompts the user.

I haven't forgotten about your calander by the way, I just got real busy the past couple of days.

THANKS seadave!!!!! I'll have to give this a try. You rock man :attn:

...and no problem about the calendar thing. I think I might just settle for a more simple sorted list type view type of thing :shrug:

gvblake22 · Mar 25, 2006

Ok, this works great (although I had to modify it a little bit) to fit my specific situation. The problem I just realized though is that this text box needs to accept decimal points (".")
This is a text box to enter a distance measurement so it can very easily be "10.5" or something... Any ideas?

seadave77 · Mar 26, 2006

Just add the ascii code for a point. You can find the ascii codes at http://www.lookuptables.com/.

The code you are looking for is 46. So you just change your statement to reflect this.

Original

Code:

If Asc(Right(Text1.Text, 1)) < 48 Or Asc(Right(Text1.Text, 1)) > 57 Then
    MsgBox "Must be a number."
End If

Modified

Code:

If Asc(Right(Text1.Text, 1)) > 48 And Asc(Right(Text1.Text, 1)) < 57 or Asc(Right(Text1.Text,1)) = 46 Then
    MsgBox "This is a number or a decimal point."
Else
    MsgBox "This is not a valid entry."
End If

gvblake22 · Mar 26, 2006

WOO HOO! It works! Thanks man. Very good idea using ASCII codes. I never would have thought of that or known what the codes were. Thanks for the link and all the help. I really REALLY appreciate it man!

gvblake22 · Mar 28, 2006

Ok, seadave, I've got another question for ya... :welcome:

I need to search a list of dates and pick out only those dates that are of a certain month (day and year is irrelevant for now). But the problem is that the dates are entered like 3/28/2006 and the if statement won't recognize a cell formatted as mmmm to display a date as the full month spelled out ("March" in this case).
So I was thinking that I need to search the original 3/28/2006 date and use a wildcard identifier of some kind so it will search for "3/*******" and recognize that as "March" and "10/*******" as "October", etc...
I can't figure out how to get it to use wildcards. So if you know how to do it or have a better idea, then please let me know

Oh yeah, and I just found out that although your previous post solved my problem for the most part, that code doesn't recognize the number 9 as a number... Know where I might have went wrong?

seadave77 · Mar 28, 2006

I see the 9 problem. Change < 57 to < 58.

As for the date I would just split it. Try something like this.

Code:

'Cells(1,1) in this example is 03/26/2005

Debug.Print Cells(1, 1)

Dim splitDate() As String

splitDate = Split(Cells(1, 1), "/")

Debug.Print splitDate(0)
Debug.Print splitDate(1)
Debug.Print splitDate(2)

You should be able to see what is happening.

gvblake22 · Mar 28, 2006

seadave77 said:
I see the 9 problem. Change < 57 to < 58.

Cool thanks.

And I think I got the date thing figured out on my own actually. Ended up reformatting the vbGeneralDate from the cell within the code to show just the month with the custum format of "mmmm" using the following code:

Code:

Format$(MonthVariable, "mmmm")

Now I just have to put that into a series of If statements to get it to do what I want. But at least it does what I want now! Gotta love the frustration of programming

Thanks for the quick reply though seadave!! :santa:

Data Verification in VBA (Visual Basic in Excel)

gvblake22

Member

seadave77

Member

gvblake22

Member

gvblake22

Member

seadave77

Member

gvblake22

Member

gvblake22

Member

seadave77

Member

gvblake22

Member

Similar threads