Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to do validation ???
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 -- Message posted from http://www.ExcelForum.com |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to do validation ???
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 -- Message posted from http://www.ExcelForum.com |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to do validation ???
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. -- Message posted from http://www.ExcelForum.com |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to do validation ???
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 -- Message posted from http://www.ExcelForum.com |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to do validation ???
=ISNUMBER(A3) in Worksheet Formulas, but
IsNumeric(argument) in VBA "mangesh_yadav " wrote in message ... Well, you have to use it like this If Not IsNumber(TextHoursSpent.Value) Then MsgBox "You must enter an integer. " Exit Sub End If - Mangesh --- Message posted from http://www.ExcelForum.com/ |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to do validation ???
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 -- Message posted from http://www.ExcelForum.com |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to do validation ???
Maybe:
If LCase(Trim(Me.TextProjectName.Text)) Like "[a-z]##-###" Then Me.Label1.Caption = "ok" Else Me.Label1.Caption = "not ok" End If "unplugs <" wrote: 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? --- Message posted from http://www.ExcelForum.com/ -- Dave Peterson |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to do validation ???
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" wrote in message ... Maybe: If LCase(Trim(Me.TextProjectName.Text)) Like "[a-z]##-###" Then Me.Label1.Caption = "ok" Else Me.Label1.Caption = "not ok" End If "unplugs <" wrote: 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? --- Message posted from http://www.ExcelForum.com/ -- Dave Peterson |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to do validation ???
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. : -- Message posted from http://www.ExcelForum.com |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Validation Data using Validation Table cell range..... | Excel Discussion (Misc queries) | |||
data validation invalid in dynamic validation list | Excel Worksheet Functions | |||
Validation (Drop down list vs simple text length validation) | Excel Programming | |||
Validation (Drop down list vs simple text length validation) | Excel Programming | |||
Validation (Drop down list vs simple text length validation) | Excel Programming |