How to do validation ???

U

unplugs

I face a problem in validation. How to do the validation whe
programming in VBA? I had do the validation to request user fo
entries. Anyway, I don't know how to do for the following features:
1) For Project Name, it must be enter like this: ?##-####

? = alphabet
#= integer

2) For Hours Spent, it must be enter integer only, and not alphabet o
other sign.

Below is my code:



Private Sub SubmitButton_Click()

Range("C3") = " Project Name "
Range("D3") = " Project Element "
Range("E3") = " Hours Spent "

Sheets("Sheet1").Activate
NextRow = Application.WorksheetFunction.CountA(Range("A:A")) + 4


If TextProjectName.Text = "" Then
MsgBox "You must enter your project name."
Exit Sub
End If

If TextProjectElement.Text = "" Then
MsgBox "You must emter your project element."
Exit Sub
End If

If TextHoursSpent.Value = "" Then
MsgBox "You must enter your hours spent."
Exit Sub
End If

Cells(NextRow, 3) = TextProjectName.Text
Cells(NextRow, 4) = TextProjectElement.Text
Cells(NextRow, 5) = TextHoursSpent.Value

TextProjectName.Text = ""
TextProjectElement.Text = ""
TextHoursSpent.Value = ""

TextProjectName.SetFocus

End Sub




Thousands thanks to anyone that can help me on this matter.
:)

Thanks
 
M

mangesh_yadav

For numbers, VBA provides somthings like isNumber(x). And as for you
first query, you could breakup the string and then check each element.

Maybe, you could get some better responses from others

- Manges
 
U

unplugs

Mangesh:

I add IsNumber(x) inside my code as follow:


If TextHoursSpent.Value = "" Then
MsgBox "You must enter your hours spent."
Exit Sub
Else
If TextHoursSpent.Value <> IsNumber(x) Then
MsgBox "You must enter an integer. "
Exit Sub
End If
End If


But it gave me a compile error message which is "Sub or Function no
define"... I'm still new in vba... Probably u can guide me on this..?

Thanks a lot... and hope I can hear from anyone that know the solutio
for the 2 features above that I mention.
 
M

mangesh_yadav

Well, you have to use it like this

If Not IsNumber(TextHoursSpent.Value) Then
MsgBox "You must enter an integer. "
Exit Sub
End If

- Manges
 
U

unplugs

Thanks a lot Mangesh and David McRitchie... I'm able to work on th
validation for integer part.

How about the first case? If I want to do validation for Project Name
that it must be enter like this: ?##-####

? = alphabet
#= integer


Anyone got any idea
 
D

Dave Peterson

Maybe:

If LCase(Trim(Me.TextProjectName.Text)) Like "[a-z]##-###" Then
Me.Label1.Caption = "ok"
Else
Me.Label1.Caption = "not ok"
End If
 
D

David McRitchie

I'm just starting on Regular Expressions but you can eliminate the
LCase by using
If Trim(Me.TextProjectName.Text) Like "[A-Za-z]##-###" Then

Until Dave's posting I was ready to post using [0-9][0-9][0-9] instead of ###

I set up a little table and macro to check RegExpr in a sandbox see
Extraction of a Group of Digits and Dashes, from postings by Harlan Grove
http://www.mvps.org/dmcritchie/excel/grove_digitsid.htm
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

Dave Peterson said:
Maybe:

If LCase(Trim(Me.TextProjectName.Text)) Like "[a-z]##-###" Then
Me.Label1.Caption = "ok"
Else
Me.Label1.Caption = "not ok"
End If

unplugs < said:
Thanks a lot Mangesh and David McRitchie... I'm able to work on the
validation for integer part.

How about the first case? If I want to do validation for Project Name,
that it must be enter like this: ?##-####

? = alphabet
#= integer

Anyone got any idea?
Click to expand...
Click to expand...
 
U

unplugs

Thanks a lot David McRitchie and Dave!!! I hope I will have suc
knowledge so that I can help others !!
:)

Thanks a lot! I 'm working fine with validation now.
:
 
Top